Excel Userform With Multiple Pages

Excel VBA Multipage Control On Userform 


        For this VBA example ,we have a list of personnel. We will use a userform when editing the details about personnel. The userform with multipage control provides more faster data entry on the worksheet.        
        VBA multipage control contains two page control. At Page 1 (named as Page3 in our template), the user can fill in personnel informations (name,address,city,phone,birthdate,birthplace etc.)
At Page 2 (Page4 in our template), items of combobox (for choosing city)  can be added.


excel multipage userform


excel userform multipage
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

Codes of this menu bar items :
Private Sub Label19_Click()
Workbooks.Add
End Sub
Private Sub Label20_Click()
Application.Dialogs(xlDialogOpen).Show
End Sub

Private Sub Label21_Click()
ActiveWorkbook.Save
End Sub

Private Sub Label22_Click()
Application.Dialogs(xlDialogSaveAs).Show
End Sub

Private Sub Label23_Click()
UserForm1.Hide
ActiveSheet.PrintPreview
UserForm1.Show
End Sub

Private Sub Label24_Click()
ActiveSheet.PrintOut
End Sub

Private Sub Label25_Click()
UserForm1.Hide
End Sub

Private Sub Label18_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal Y As Single)
Label18.Font.Bold = True
Label18.Font.Size = 11
Frame2.Visible = True
End Sub


When the menu bar control is active ⤵️
excel userform menu

excel vba multipage
      🔹 VBA Multipage control allows us to use the area of the userform more efficiently by grouping the userform elements. Multipage control can be seen on the Toolbox in VBA Editor (to open VBA Editor ,press Alt + F11 keys on keyboard) .
        🔹 To add a Multipage control, the Multipage icon on the toolbox is clicked and dragged it onto the userform. When you add the 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 these pages.

       🔹 The properties of the pages of the Multipage control can be viewed in the Properties window on the lower left and changes can be made here.
         🔹 The choosed controls such as textbox, button, label from the toolbox can be added to the selected page tab of the 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 VBA Multipage control's first page tab⤵️
excel multipage control on userform


    The content of VBA Multipage control's second page tab ⤵️
Userform with Multiple Pages

Also these controls can be used in this userform :
✔️ 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.)

excel userform with multipages

13 comments:

  1. You Are Doing Great Job .. Thanks For Making Excel Lovely Application.
    One Question?
    Can We Build A Complete Document System (DMS) Using Excel, Where We Store A Copy of The PDF, JPG etc Files To A Single Record (Row).
    Thanks

    ReplyDelete
  2. can you please tell me how to make it works for excel 11 on mac. Thank you

    ReplyDelete
  3. please send me sample file Vipul.srivastav12@gmail.com

    ReplyDelete
  4. cannot open for download. can you make new link for easy to download..thank you for that

    ReplyDelete
  5. i can't download the file. it shows that you are not authorized. pls help me

    ReplyDelete
  6. i want to learn basics of basics of vba to create advance userform . can you suggest any book or material......

    ReplyDelete
  7. Helo Sir,
    I need Need a Softwear For Data entry & Billing Invoice.
    Purpos For International Courier

    So Please Can You Provide me. Please.

    ReplyDelete
  8. Please send this userform to sonigaurav22@gmail.com as while I download msg shows that u r not authorized to visit this page

    ReplyDelete
  9. please send me sample file
    Praful.gajera218@gmail.com

    ReplyDelete