Excel VBA MultiPage Control on Userforms

The Excel VBA MultiPage control is a useful way of logically grouping the controls of your userform. Especially if you have many (just think of the Format Cells window).

In this blog post, we will look at how to create and use the MultiPage Control on your Excel VBA userforms, and also how to modify its properties and refer to it during runtime.

For this example, we have a list of customers. And we want to use a userform when editing the details about these customers. This will provide greater validation and more reliable data entry than editing the details directly on the sheet.

We would also like to run common tasks from this userform. So the Excel VBA MultiPage control has been used to group the customer details, and common tasks onto separate tabs, or pages.

This image shows the customer details tab.

And this shows the Tasks tab.

So let’s have a look at how to use the Excel VBA MultiPage Control.

Watch the Video – Excel VBA MultiPage Control

Inserting the Excel VBA Multipage Control

We have the beginnings of a userform below. And firstly we need to insert the MultiPage control.

Click the MultiPage Control button in the Toolbox, and click and drag to draw it onto the userform (do not confuse it with the TabStrip Control next to it).

Editing the Properties of Each Page

Once the control is inserted, we need to edit some key properties about each page of the control.

You can also edit properties of the MultiPage control, such as its name. As it is the only one on the form, this is left as MultiPage1.

You can edit these properties in two main ways. Firstly you could right mouse click on the page you want to edit.

From the menu that appears, you can insert additional pages (you get 2 with the control to start with). This is useful.
If you click the Rename option, you get the window below.

From here, you can change the Caption of the page. This is what the user will see in the page tab.
You can also set the Accelerator Key. This is a shortcut for the page.

In the image above, the letter d is used. This means the user can press Alt + d to access the page, instead of clicking the tab. The letter d of the caption Details will be underlined to show the user this (you can see this in the image at the beginning of the blog post).

Finally, you can set the Control Tip Text in this window. This is the text that the user sees when they position the cursor over the page.

Another way of setting the page properties is by using the Properties window.

Here you have more options including naming the page. This is important as we can refer to the page in VBA code by using this name. Name it something meaningful like pgDetails for easy reference.

You can also see the Index property for the page here. The first page has an Index of 0. This will be useful shortly when referring to the page.

Selecting a Page Tab of the Multipage Control

You will need to know some VBA to work with the MultiPage control, and something you will probably need is to be able to select a page tab during runtime.

In the video of this blog post, we have a scenario where if the user has a cell selected in the customer list, we want the userform to go to the Details page tab, whilst if the cell is blank go to the Tasks page tab.

Now here is something unexpected.

To select a page tab, you use its Value property.

So to select the first page tab of the control you would write.

MultiPage1.Value = 0

Remember that MultiPage1 is the name of our MultiPage control, and you can change this property.
And to select the second page tab of the control you would write.

MultiPage1.Value = 1

It is an unusual use of the Value property, but it is what you do with the Excel VBA MultiPage control.

This can be added to the Initialize event of the form so that the page is selected as the form opens.

Changing MultiPage Control Properties in VBA

We have looked at changing the properties of the page tabs and MultiPage control itself manually. But how can this be done using VBA.

For example, maybe I want the Caption of a page to change depending on the details of the currently selected customer.

We can modify the Caption property of a Page with this code.

MultiPage1.Pages(0).Caption = Cells(i, 1).Value & " - " & Cells(i, 2).Value

This example uses the Pages collection in the same way that you might be used to using the Worksheets, or Workbooks collections.

0 is the Index value of the first page tab.

You can also bypass the Pages collection and refer to the Page using its name. This is shown below (remember the first page was named pgDetails).

MultiPage1.pgDetails.Caption = Cells(i, 1).Value & " - " & Cells(i, 2).Value

The rest of this code is pulling data from the worksheets and assigning it to a Caption.

You can see in the image below the caption Details is replaced with 1110 – Elizabeth Lincoln (the currently selected customer).

The Excel VBA MultiPage Control can be very useful when designing a userform with many controls. You can group them meaningfully so user find your forms easy to use.