Excel VBA: Using The MultiPage Control In Your Userforms - KING OF EXCEL


KINGEXCEL.INFO ( KING OF EXCEL ) Welcome KINGEXCEL.INFO - Nothing Is Unable ... About Excel Tricks, Learning VBA Programming, Dedicated Software, Accounting, Living Skills ...

Saturday, December 14, 2019

Excel VBA: Using The MultiPage Control In Your Userforms

The 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 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.
MultiPage Control on a userform

And this shows the Tasks tab.
Tasks tab of the MultiPage Control
So lets have a look at how to use the MultiPage Control.

Watch the Video

Inserting the 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).
Inserting the MultiPage Control

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.
Editing a Page
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.
Renaming a Page of the MultiPage Control
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.
Setting Properties for a Page in 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 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).
Changing the Caption property with VBA
The 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.
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment