Creating a Tab Control in a UserForm in Excel VBA

Step 1: Insert a UserForm

1. Open Excel and press ALT + F11 to open the VBA Editor.
2. In the VBA Editor, go to Insert > UserForm. A new blank UserForm will appear.
3. Rename the UserForm for clarity. In the Properties Window, change the Name property of the UserForm to ufTabExample.

Step 2: Add a Tab Control

1. In the Toolbox, find the « MultiPage » control (which functions as a Tab Control).
2. If the Toolbox is not visible, press CTRL + T or go to View > Toolbox.
3. Click on the MultiPage control (it looks like multiple tabs) and draw it on the UserForm.
4. Rename the MultiPage control for clarity:
   • Select the MultiPage control.
   • In the Properties Window, change the Name to mpTabs.

Step 3: Add Tabs to the Tab Control

By default, the MultiPage control contains two pages (tabs). You can add more tabs using VBA or manually.

Manually Adding Tabs:

1. Right-click on the MultiPage control.
2. Select « New Page » to add a new tab.
3. Rename each tab using the Caption property.

Using VBA to Add Tabs Dynamically: If you want to create tabs dynamically, you can use the following VBA code:

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim tabNames As Variant
    tabNames = Array("General", "Settings", "Advanced")
    ' Remove default tabs before adding new ones
    Do While mpTabs.Pages.Count > 0
        mpTabs.Pages.Remove 0
    Loop
    ' Add tabs dynamically
    For i = LBound(tabNames) To UBound(tabNames)
        mpTabs.Pages.Add
        mpTabs.Pages(i).Caption = tabNames(i)
    Next i
End Sub

This code runs when the UserForm loads, removing any existing tabs and adding three new ones: General, Settings, and Advanced.

Step 4: Add Controls to Each Tab

You can manually add controls (labels, textboxes, buttons, etc.) to each tab. Each page acts like a container for controls.

Manually Adding Controls:

1. Click on the MultiPage control.
2. Select a tab (Page1, Page2, etc.).
3. Drag and drop controls from the Toolbox onto each tab.

Adding Controls Using VBA:

You can also add controls programmatically:

Private Sub AddControlsToTabs()
    Dim txtBox As MSForms.TextBox
    Dim lbl As MSForms.Label
    Dim cmdBtn As MSForms.CommandButton
    ' Add a label to the first tab (General)
    Set lbl = mpTabs.Pages(0).Controls.Add("Forms.Label.1", "lblGeneral", True)
    lbl.Caption = "Enter Name:"
    lbl.Left = 10
    lbl.Top = 10
    ' Add a textbox to the first tab (General)
    Set txtBox = mpTabs.Pages(0).Controls.Add("Forms.TextBox.1", "txtName", True)
    txtBox.Left = 100
    txtBox.Top = 10
    txtBox.Width = 150
    ' Add a button to the second tab (Settings)
    Set cmdBtn = mpTabs.Pages(1).Controls.Add("Forms.CommandButton.1", "btnSave", True)
    cmdBtn.Caption = "Save Settings"
    cmdBtn.Left = 10
    cmdBtn.Top = 10
End Sub

This code adds:

• A label and textbox to the first tab (General).
• A button to the second tab (Settings).

Step 5: Write VBA Code to Handle User Actions (Optional)

You may want to handle user interactions such as:

• Switching between tabs.
• Retrieving input values.
• Performing actions when a button is clicked.

Example: Handling Tab Change Event

You can detect when a user switches between tabs:

Private Sub mpTabs_Change()
    MsgBox "You switched to tab: " & mpTabs.Pages(mpTabs.Value).Caption
End Sub

Example: Handling Button Click in a Tab

You can define an event when the Save Settings button is clicked:

Private Sub btnSave_Click()
    MsgBox "Settings Saved!", vbInformation, "Success"
End Sub

Final VBA Code (Complete Version)

Here’s a complete version of the code, combining all the steps:

Private Sub UserForm_Initialize()
    Dim i As Integer
    Dim tabNames As Variant
    tabNames = Array("General", "Settings", "Advanced")
    ' Remove default tabs before adding new ones
    Do While mpTabs.Pages.Count > 0
        mpTabs.Pages.Remove 0
    Loop
    ' Add new tabs dynamically
    For i = LBound(tabNames) To UBound(tabNames)
        mpTabs.Pages.Add
        mpTabs.Pages(i).Caption = tabNames(i)
    Next i
    ' Call function to add controls
    AddControlsToTabs
End Sub

Private Sub AddControlsToTabs()
    Dim txtBox As MSForms.TextBox
    Dim lbl As MSForms.Label
    Dim cmdBtn As MSForms.CommandButton
    ' Add a label to the first tab
    Set lbl = mpTabs.Pages(0).Controls.Add("Forms.Label.1", "lblGeneral", True)
    lbl.Caption = "Enter Name:"
    lbl.Left = 10
    lbl.Top = 10
    ' Add a textbox to the first tab
    Set txtBox = mpTabs.Pages(0).Controls.Add("Forms.TextBox.1", "txtName", True)
    txtBox.Left = 100
    txtBox.Top = 10
    txtBox.Width = 150
    ' Add a button to the second tab
    Set cmdBtn = mpTabs.Pages(1).Controls.Add("Forms.CommandButton.1", "btnSave", True)
    cmdBtn.Caption = "Save Settings"
    cmdBtn.Left = 10
    cmdBtn.Top = 10
End Sub

Private Sub mpTabs_Change()
    MsgBox "You switched to tab: " & mpTabs.Pages(mpTabs.Value).Caption
End Sub
Private Sub btnSave_Click()
    MsgBox "Settings Saved!", vbInformation, "Success"
End Sub

Example Output

When running this UserForm:

• It displays three tabs: General, Settings, Advanced.
• The General tab has a label and textbox for user input.
• The Settings tab has a button to save settings.
• A message box appears when switching tabs.
• Clicking the Save Settings button triggers a message.

Conclusion

This guide provided a detailed step-by-step process for adding a Tab Control (MultiPage) in an Excel VBA UserForm. It covered: ✅ Adding a MultiPage control.
✅ Creating tabs dynamically.
✅ Adding controls to specific tabs.
✅ Writing VBA code to handle user interactions.