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 :
✔️ Save As
✔️ Print Preview
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 :