Excel VBA Userform MultiPage

In this userform example, unlike other userform examples, we have added MultiPage control and a Menu bar on the userform.

Excel VBA Multipage control allows to use the area of userform more fruitful by grouping the userform controls. Multipage control can be seen on the Toolbox in VBA Editor.

Userform multipage control contains two page control. At Page 1 (named as Page3 in our template), personnel informations (name,address,city,phone,birthdate,birthplace etc.) can be added,deleted,changed with listbox,textbox,combobox controls.
At Page 2 (Page4 in our template), city names are added to the Data2 worksheet using textbox, listbox and buttons on Page 2. The added city names are filled in the combobox on Page 1.

To add a Multipage control to the userform, Multipage icon on the toolbox is clicked and dragged it onto the userform. When we add Multipage control to the userform, it contains 2 pages by default. A new page can be added, deleted, renamed or relocated from the menu that is opened by right-clicking on one of this pages.

The properties of pages of the Multipage control can be viewed in VBA Properties window on lower left and the changings can be made here.

The choosed controls such as textbox, button, label from the toolbox can be added to the selected page tab of Multipage control. In VBA, the following codes are used to select the page tabs of Multipage:
To select the first page :           MultiPage2.Value = 0             (In our template , Multipage control is named as MultiPage2)
To select the second page :      MultiPage2.Value = 1
Or to add a vertical scrollbar to the first page :
MultiPage2.Pages(0).ScrollBars = fmScrollBarsVertical

The content of Multipage control’s first page tab :
The following operations can be performed with the VBA controls on Page 1 :
✔️ Adding new record
✔️ Deleting record
✔️ Updating record
✔️ Label to see total data
✔️ Navigating between items of listbox with spin buttons (down/up)
✔️ Assigning sort numbers for each record (when an item is deleted from listbox, the sequence numbers are set again.)

The content of Multipage control’s second page tab :
With the userform elements in Page 2, cities are entered in column A of the Data2 worksheet.To combobox on Page 1, city names are filled in as unique and sorted items.
To populate unique and sorted values to Excel combobox :

Dim x As Long, a, b As Long, c As Variant
       
 For x = 2 To Sheets("Data2").Cells(Rows.Count, "A").End(xlUp).Row
If WorksheetFunction.CountIf(Sheets("Data2").Range("A2:A" & x), Sheets("Data2").Cells(x, 1)) = 1 Then
ComboBox1.AddItem Sheets("Data2").Cells(x, 1).Value
End If
Next
     
For a = 0 To ComboBox1.ListCount - 1
  For b = a To ComboBox1.ListCount - 1
      If UCase(ComboBox1.List(b)) < UCase(ComboBox1.List(a)) Then
c = ComboBox1.List(a)
    ComboBox1.List(a) = ComboBox1.List(b)
    ComboBox1.List(b) = c
       End If
   Next
 Next

Also, we added a menu bar on the userform and we listed the items of the menu as labels on a frame control. Items of this menu :
✔️ New
✔️ Open
✔️ Save
✔️ Save As
✔️ Print Preview
✔️ Print
✔️ Close

VBA codes of menu bar items :

Private Sub Label19_Click()                     'New menu
Workbooks.Add
End Sub
Private Sub Label20_Click()            'Open menu
Application.Dialogs(xlDialogOpen).Show
End Sub
Private Sub Label21_Click()            'Save menu
ActiveWorkbook.Save
End Sub
Private Sub Label22_Click()           'Save as menu
Application.Dialogs(xlDialogSaveAs).Show
End Sub
Private Sub Label23_Click()          'Print preview menu 
UserForm1.Hide
ActiveSheet.PrintPreview
UserForm1.Show
End Sub
Private Sub Label24_Click()          'Print menu
ActiveSheet.PrintOut
End Sub
Private Sub Label25_Click()         'Close userform
UserForm1.Hide
End Sub

Userform menu bar on VBA Window :

Result :

📥 Download sample userform