Tutorial Details
Downloadable Files: Excel File
Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Multi-Page UserForm

Add to Favorites
Author: | Edits: don

You can have multiple tabs of data on a single UserForm and this allows you to, effectively, have multiple pages of data inside the form. Here, you'll learn how to create this multi-page setup, how to add data to each page, and how to reference the pages programmitcally.

048a38b63a9607a95fe349e9f5784648.png

Sections:

Add Multiple Pages to a UserForm

Add Controls and Data to Pages in a UserForm

Rename Page Tabs for a UserForm

Add More MultiPage Tabs to a UserForm

ToolTip To Make Using Tabs Easier

Reference the UserForm Pages Programmitcally

Completed MultiPage UserForm

Notes

Add Multiple Pages to a UserForm

Start with an empty UserForm; this will make the design and structure much easier to handle.

(If you can't start with an empty form, make the current one extra large and add the MultiPage elements and when you are finished building it, reposition everything and shrink the form back down.)

Select the MultiPage control from the Toolbox. (If you don't see the toolbox, click the form and go to View > Toolbox in the top navigation menu).

7103b45fc8a287cf3d50c4f996b1ff34.jpg

Click and drag your mouse on the form to place the MultiPage control.

Note: make sure to leave a section at the bottom of the form for some buttons that are not page-specific, such as the "Close" or "Exit" button that closes the form; you don't want this button hidden somewhere in the pages of the form so that the user has to search to find it.

fb5f9249ffd336dd07cf34776f36cc87.png

Run the form now and you will see that you have two separate and distinct tabs.

Now, let's build-out the tabs.

Add Controls and Data to Pages in a UserForm

Click the page tab that you want to add a control to and simply place the control within that page tab.

It's as simple as that.

cb0c18c302cd56df861f34e1fa4d940a.png

It's a good tip to leave a lot of extra space on the UserForm before you finalize its design; this will allow you to easily move controls between the pages by dragging them off of one page and onto the form and then navigate to the new page to place the control there.

Designing a form can take a long time, but it's worth it to get one that is easy for people to understand and use.

Rename Page Tabs for a UserForm

You should have descriptive names for each page tab so that users know what data is on each tab.

To rename them, click the desired page tab > go to the Properties window (F4) and edit the Caption property.

7a185f037c687312887059ff6bcbb8f2.jpg

You can also do this by right-clicking the tab and clicking Rename

235b441550526fd6aa4de314831cb272.jpg

Add More MultiPage Tabs to a UserForm

Right-click the tabs and click New Page

4517fc245b21512e492ab89e0e926cd6.jpg

After you add the new page, don't forget to rename it so you know what content will go on it.

ToolTip To Make Using Tabs Easier

You can add text that appears when the user hovers over a tab on the form and this is great for explaining the purpose of an individual page/tab.

Right-click the tab and click Rename

0ca2fe8ce0836a23ceda48fa2790c051.jpg

In the window that opens, type some helpful text in the Control Tip Text section.

5ab52700ca3b622e8a7d87e80405a5ba.png

Now, when you run the form and hover over this tab, it will look like this:

b5c13c459971d3aa18a5df9ed5bdc30f.png

Reference the UserForm Pages Programmitcally

You reference controls in the UserForm the same way that you would if there was only one page and no tabs; none of that changes because each control has its own unique name.

However, you may want to refer to the pages on a MultiPage form and this is how you do that.

You first reference the name of the MultiPage control and then you can reference the pages within that control.

MultiPage1.Page1.Caption

This gets the name of the first page tab.

MultiPage1 is the name of the MultiPage control.

Page1 is the name of the page. If you have multiple MultiPage controls, they can contain pages with the same name and this is why we first have to use the name of the MultiPage control in order to get information about a specific page.

Caption is the property that controls the text displayed on the tab.

Index Numbers

You can also reference pages using their index number. This is a bit more confusing than using the name of the page but it is helpful when accessing the pages from within a loop.

MultiPage1.Pages(0).Caption

MultiPage1 is the name of the MultiPage control.

Pages is the page object.

(0) is the index number.

Note: the first page tab has an index number of zero! The second page has an index number of one! This is easy to forget and this is why, when referring to a specific page, it is often easier to use its name instead of its index number. 

MessageBox Examples

The sample file for this tutorial includes some examples of this code that will output the caption of a page into a MessageBox so that you can visually ensure that it works like you expect it to; these lines of code were added to the top of the UserForm_Initialize event.

Completed MultiPage UserForm

Here is a screenshot of the completed MultiPage form that is included in the sample file for this tutorial. This is the standard UserForm that we have been working with throughout all of the tutorials here, except that the controls have now been broken-up onto two separate pages/tabs.

c1238da7d66d45e90ca74ebb61b9b4aa.png

Make sure to download the sample file for this tutorial to get this form.

Notes

MultiPage controls should only be used on a form where you have a lot of information that fits into distinctly different groups or categories. In this example, this form probably doesn't contain enough information to warrant having separate tabs, though it serves as a good example of how to use this feature.

For a good example of a MultiPage (tab) form design, right-click any cell in Excel and click Format Cells; that is a perfect example of when multiple groups of information should be broken up into smaller pieces.

MultiPage controls are not difficult to use, just make sure to keep the main buttons, such as the one that the user clicks to close the form, outside of any MultiPage elements or it will make it more difficult to use the form.

Make sure to download the sample file for this tutorial to see the form above. Also, two lines have been added to the top of the UserForm_Initialize event to show you how you can reference the pages, within the MultiPage control, using VBA.

Question? Ask it in our Excel Forum


Downloadable Files: Excel File