control is very useful for UserForms that must display many controls. The
control lets you group the choices and place each
Figure 14-18 shows an example of a UserForm that contains a MultiPage control. In this case, the control has three pages, each with its own tab.
Figure 14-18: MultiPage groups your controls on pages, making them accessible from a tab.
This example is available on the companion CD-ROM. The file is named multipage control demo.xlsm .
The Toolbox also contains a control named TabStrip , which resembles a MultiPage control. However, unlike the MultiPage control, the TabStrip control is not a container for other objects. The MultiPage control is much more versatile, and I've never had a need to actually use the TabStrip control.
Using a MultiPage control can be a bit tricky. The following are some things to keep in mind when using this control:
The tab (or page) that's displayed up front is determined by the control's Value function. A value of displays the first tab, a value of 1 displays the second tab, and so on.
By default, a MultiPage control has two pages. To add a new page in the VBE, right-click a tab and select New Page from the shortcut menu.
When you're working with a MultiPage control, just click a tab to set the properties for that particular page. The Properties window will display the properties that you can adjust.
You might find it difficult to select the actual MultiPage control because clicking the control selects a page within the control. To select the control itself, click its border. Or, you can use the Tab key to cycle among all the controls. Yet another option is to select the MultiPage control from the drop-down list in the Properties window.
If your MultiPage control has lots of tabs, you can set its MultiRow property to True to display the tabs in more than one row.
If you prefer, you can display
The TabOrientation property determines the location of the tabs on the MultiPage control.
For each page, you can set a transition effect by changing the TransitionEffect property. For example, clicking a tab can cause the new page to push the former page out of the way. Use the TransitionPeriod property to set the speed of the transition effect.
The example in this section uses the Microsoft Date and Time Picker Control. Although this is not an Excel control (it's installed with Windows), it works fine in a UserForm.
To make this control available, add a UserForm to a workbook and follow these steps:
Activate the VBE.
Right-click the Toolbox and choose Additional Controls.
Select View Toolbox if the Toolbox is not visible.
In the Additional Controls dialog box, scroll down and place a check mark
Your Toolbox will display a new control.
Figure 14-19 shows the Date and Time Picker Control in a UserForm, along with the Property window. The Format property determines whether it works with dates or times.
Figure 14-19: The Date and Time Picker Control in a UserForm.
Figure 14-20 shows this control being used. Clicking the drop-down button displays a calendar. When the
Figure 14-20: Inserting a date using the Date and Time Picker Control.
When the UserForm is displayed, the Date and Time Picker displays the current date by setting its Value property in the UserForm_Initialize procedure:
Private Sub UserForm_Initialize() DTPicker1.Value = Date End Sub
The code to handle the Insert button click is as
Private Sub InsertButton_Click() ActiveCell = DTPicker1.Value ActiveCell.Columns.EntireColumn.AutoFit End Sub
This example, named date and time picker.xlsm , is available on the companion CD-ROM.