 |
| <<Convert Excel Spreadsheets to Web Pages | Trading Software That Operates Within Excel | Convert Excel, Access & Other Databases | Merge Excel Files>> |
|---|
|
|
|
Workbook
Download
This is a zipped Excel Workbook to go with this lesson.
MultiPage Control
In this lesson we will look at the MultiPage control, arguably the most useful controls to use if your UserForm will be containing many different controls and/or you wish to have different controls associated with different aspects of your project. The other thing that we can do with a MultiPage is make our UserForm behave in the same manner as any one of Excel's standard Wizards. A Wizard by definition is an aid that steps you through a particular process. An example of this would be the Pivot Table Wizard or Chart Wizard.
To attach a MultiPage control to a UserForm, we simply insert a UserForm into our Workbook, ensure the Control Toolbox is displayed, if it does not show by default when you insert the UserForm, simply select the UserForm and go to View>Toolbox, or click the hammer and spanner symbol on the Standard Toolbar. The MultiPage control is normally situated on the third row down, second control in. As with all controls, if you are unsure, just hover your mouse pointer over the control and the name will appear for you. Click the MultiPage control and then click your UserForm and it will be embedded on there. By default, your MultiPage will have two pages, Page1 and Page2. To give yourself an idea of what this control does, simply ensure your UserForm (no the MultiPage) is selected and go to Run>Run sub/UserForm or push F5. This will display the UserForm complete with a MultiPage control on it. You will notice that by clicking Page1 and Page2, the MultiPage form control will change page. Click the "X" on the top right hand side of the UserForm to close the UserForm. As with all controls, it pays to give each one a meaningful name. In other words, the default for the first MultiPage you use on a UserForm is MultiPage1, the second is MultiPage2 and so on, so change these to a name that will have some meaning to you, or the project they are being used in. If two pages is not enough for your MultiPage, we can easily change this by right clicking on any one of the MultiPage tab names (eg; Page1) and then simply select NewPage. You would do this for each page you want inserted. You will also notice when you right click that you have the option to delete a page, rename a page or move a page. Deleting a page speaks for itself, but if you select Rename, you will be prompted to give the page a caption name, which will replace the existing one, and an accelerator* key if you want one.
* An accelerator key is a single character used as a shortcut for selecting an object. Pressing the Alt key, followed by the accelerator key gives focus to the object and initiates one or more Events associated with the object. The specific Event or Events initiated varies from one object to another. If code is associated with an Event, it is processed when the Event is initiated. Also known as keyboard accelerator, shortcut key and keyboard shortcut.
There is also a space to type some text for the ControlTipText property. If you are not already familiar with this, the ControlTipText is activated whenever the user hovers the mouse over a control. It then displays the text you have used for its property.
To see what I mean for both of these, right click on the tab Page2, select Rename, replace the caption Page2 with the word Test, type a lower-case a in the accelerator key and then in the ControlTipText box type "Push Alt + a to activate me". Then click back on Page1, and run the UserForm by pressing F5. Hover your mouse pointer over the Test Page to see your TipText, then as the message sais, hold down the Alt key and press a and the page Test should be activated. Again, click the top right "X" to close the UserForm. The only other item we have on the right-click menu when right-clicking on a MultiPage tab name is the Move option, which simply changes the page order. Using this is fairly self-explanatory, so I won't go into any detail.
The Properties that we have just changed, ie; Caption and Accelerator, can also be changed in the Property window of the MultiPage control. If this is not visible already, right-click anywhere on the MultiPage control other than the tab names and select Properties. Be aware though, that when you do this, many of the Properties you are looking at are only applicable to the page of the MultiPage that you have selected at the time. Also, as with most Control Properties, we can change these Properties at Run-time via VBA code.
As mentioned above, it is very important to understand that each page in a MultiPage Control has its own Properties. This simply means that if we change any one of its Properties at Run-time, we must tell Excel the page we are referring to. The method we use to do this is by accessing the Pages Collection of the MultiPage Control. This is very similar to the method you would use when accessing the Sheets Collection of a Workbook. There are five methods we can use to access an individual page of a MultiPage. These are:
Let's look at each of these in turn!
The Pages Collection with a Numeric index.
An example of this would be:
MsgBox MultiPage1.Pages(0).Caption
It is important to note here that the index 0 always refers to the first page of a MultiPage Control. The second page would be Pages(1) etc.
Using the Pages Collection with the Item Method.
MsgBox MultiPage1.Pages.Item(1).Caption
It is important to note here that the Item Index 0 always refers to the first page of a MultiPage Control. The second page would be Pages.Item(1) etc.
Yet another method we could use is the Page Name Property of a MultiPage
MsgBox MultiPage1.Pages("Page2").Caption
It is important to note that the Page Name Property is not necessarily the Caption of the particular MultiPage we want. For example, we changed the Caption Property of Page2 to Test. This means that we would still refer to this page via VBA code by using the example shown above. In a nutshell this simply means you should not confuse the Caption Property with the Name Property.
One other method we can use is by using the Page Object itself and thus bypassing the Pages Collection.
MsgBox MultiPage1.Page1.Caption
Obviously, in the above example, we must have a page on our MultiPage Control with its Page Name Property set to Page1.
The final method that can be used is by using the SelectedItem Property. This method, when used in a similar way to the above examples, would always return the name of the active page of the MultiPage at the time of the code being run.
MsgBox MultiPage1.SelectedItem.Caption
Value Property
While the Pages Collection allows us to refer to any particular page of a MultiPage, it cannot be used to physically activate or select a particular page of our MultiPage. To be able to do this, we need to use the Value Property of the MultiPage. For example, if we wanted our UserForm to always default to Page1 of our MultiPage we would use
Private Sub UserForm_Initialize ()
Multipage1.Value = 0
End Sub
By placing code like this in the Initialize Event of the UserForm, our MultiPage will always present the User with the first page. Note also that we used 0 and not 1 to activate the first page. This is because 0 refers to Page1, 1 refers to Page2 etc.
There is not much else that the Value Property can be used for, other than as shown in the above example. In other words, we can only parse a whole number value to it or have the Value Property parsed back to us. By this I mean if we needed to check which page of the UserForm was active before running some code, we could simply use
Private Sub UserForm_Initialize ()
If Multipage1.Value = 0 Then
MsgBox "Page1 is active"
End If
End Sub