When a UserForm grows in complexity to the point that user experience is affected, a MultiPage control can provide a way out. By dividing the UserForm’s functionality into “pages” it can simplify what the user sees.
- Introduction to the MDUF MultiPage
- Criteria for assessing the MultiPage design option
- Specifying a MultiPage control
- Managing the Pages of a MultiPage
- Specifying controls on a Page
- Specifying Pages
- Specifying Page properties
- Specifying control positions on Pages
- Page procedures: New Page, Delete Page, Rename… , and Move…
Introduction to the MDUF MultiPage
If a UserForm has more than a dozen or so controls, usability can become a problem unless the designer takes appropriate steps to manage usability. One option is employing the MultiPage. Unlike other controls, the MultiPage control can carry controls, including MultiPage controls [1]. The purpose of the MultiPage is simplifying the appearance of complex UserForms.
The MultiPage control is capable of displaying subsets of the UserForm’s controls on its Pages, of which it can have many. In this way, the MultiPage can reduce the visual complexity of the UserForm, which, it is hoped, should make the UserForm easier to use.
However, in practice, the MultiPage forces a tradeoff. In some cases, users need to examine simultaneously the states of controls that lie on different Pages of the MultiPage. In that situation, the user is compelled to flip back and forth between the relevant pages of the MultiPage, memorizing or making notes about the information that’s about to be hidden on one page when another page becomes active. The tradeoff then is a sacrifice of usability in some circumss to gain visual simplicity in all circumstances.
The subset of situations in which using a MultiPage might put usability at risk includes those in which the visual space of the UserForm can’t be factored in a way that serves all of the UserForm’s use cases. There are several terms in that sentence that need clarification.
Visual space
The visual space of a UserForm is what we see when we look at it. It includes everything the UserForm presents, either for user input or for display.
Factorization of the visual space
MultiPage controls factor their UserForm’s visual space. By this I mean that they break the visual space into sectors that are defined by the Pages of the MultiPage. In the case of the Mac Excel MultiPage control, the factorization is exclusive. That is, we can interact with only one sector at a time.
Use case
A use case for a UserForm is a description of the way a particular class of users can interact with the UserForm. It specifies the purpose(s) of the interaction, the desired outcomes, the procedures for achieving those outcomes, the initial state conditions, and the final state conditions.
MultiPage controls inherently factorize the visual space. So a MultiPage control is helpful when the pages of the control factorize the visual space in a way that’s convenient for the use cases we care about, or even better, for all use cases. If there are several use cases that require conflicting visual space factorizations, then we might have a problem.
An example:
Suppose we have a MultiPage control for managing clients. We would like all client contact information on one page of the MultiPage. A use case for which this is convenient is creating a new client. And we would like another page for making notes about conversations with the client. Two use cases for client conversations are (a) revising the contact information and (b) making notes as we converse with the client. For these two use cases, the MultiPage described above factors the visual space in a way that parallels the use cases.
Now suppose we want to display the client telephone number on the Notes page and the Contact page, just for convenience. If we’re content to have editing capability for the phone number on the Contact page alone, then there is no difficulty. But trouble arises if we want to support editing the telephone number in both use cases. If that is a requirement, we either need editing capability on both pages, or we advise users to flip back from the Notes page to the Contact page if they want to edit the telephone number. The latter choice is an admittedly trivial example of a visual space factorization that might not work well for both use cases.
[1] I’ve rarely seen anyone use a MultiPage inside a MultiPage, though here is an example. It probably is a very bad idea from the perspective of User Experience design. But I have confirmed that Excel supports the practice. Therefore, MDUF supports the practice.
If you can’t factorize the visual space in a manner parallel to the use cases you care about, MultiPage might not be a wise choice.
Criteria for assessing the MultiPage design option
When it isn’t possible to factorize the visual space along the lines defined by the use cases you care about, consider a solution like the one we chose for the MDUF Properties Editor. The Properties Editor mDialogBoxes don’t use MultiPage controls. All controls are on the mDialogBox, in a columnar layout, alphabetized. It looks visually complex at first, but after a few uses, it’s easy to find what you want if you need to make changes.
Any alternative to using a MultiPage must make it easy for users to find what they need. In the case of the MDUF Properties Editor UserForms, we accomplish this by using an alphabetized columnar layout. Whatever alternatives you consider, evaluate them against each other for the use cases of greatest importance.
Specifying a MultiPage control
There are two sets of MultiPage properties on the FormSpec worksheet, shown in the table below. To edit properties shown in the column headed Worksheet edit the corresponding cell on the FormSpec worksheet. To edit the properties shown in the columns headed Properties Editor use the Properties Editor. With the exception of the Page property, all properties take on the same values in MDUF as they do in Excel.
The Page property is different. It specifies the name of the page that hosts this MultiPage control, but only when this MultiPage control is hosted on a Page of another MultiPage.
| Worksheet | Properties Editor | Properties Editor |
| Name | ControlTipText | MultiRow |
| Left | Enabled | Style |
| Top | Font | TabFixedHeight |
| Width | FontSize | TabFixedWidth |
| Height | FontStrikethrough | TabOrientation |
| Page | FontStyle | Tag |
| TabIndex | FontUnderline | Value |
| TabStop | HelpContextID | Visible |
| ForeColor | ||
| BackColor |
Managing the Pages of a MultiPage
You can create, rename, delete, or reorder Pages in a straightforward manner by editing the contents of the FormSpec worksheet manually. Although these manual procedures are available and effective, they are not recommended. They are laborious and error-prone. The preferred methods employ MDUF ribbon commands. Commands are available to support the needs of a variety of situations, described below.
Renaming an existing Page of a MultiPage
After you create Pages, you can rename them if you like. If the Pages contain no controls, you can rename them by editing the Name cell of each of their records. To rename Pages that do contain controls, it’s more convenient to use the MDUF ribbon command This Control>Rename… . This command is more convenient because the records of controls that are hosted on Pages of a MultiPage contain the names of those Pages. The Rename Control command updates those records for you.
Before invoking the MDUF ribbon command, select a cell in the record of the Page you want to rename. When you invoke the command, MDUF presents the Rename Page mDialogBox. Verify that the correct page is selected in the ListBox of this mDialogBox, then enter the new name of the Page and click OK.
An MDUF constraint on Page names
Excel does not require that the Page names of the Pages of a MultiPage be unique. That is, two Pages of the same MultiPage in Excel are allowed to have the same name. Because this situation can lead to unintended results, it is not permitted in MDUF. In MDUF all Pages of a given MultiPage must have unique names.
This constraint does not span across distinct MultiPages. That is, two different MultiPages can each have a Page with name Page1, for example.
Although MDUF prevents you from assigning the same name to two different Pages of a given MultiPage, when capturing legacy UserForms, you might encounter a legacy UserForm that violates this constraint. Prepare legacy UserForms for capture in MDUF by renaming any Pages that share names and belong to the same MultiPage. And, of course, survey the VBA code to ensure that the renaming causes no problems.
Creating a new MultiPage
To create a new MultiPage on the active FormSpec worksheet, invoke the MDUF ribbon command This FormSpec>Insert>One Control… . MDUF then presents an mDialogBox titled “Select control types to insert”. Select MultiPage and then click OK. Excel then creates a new MultiPage record at the bottom of the range of MultiPage records.
To create a new MultiPage in a position other than last among the MultiPage records, select a cell in the row above which you want the new MultiPage to appear. Then invoke the MDUF ribbon command This FormSpec>Insert>Multiple Controls. MDUF then inserts a new blank record at that point. If the selection has N rows when you invoke this command, MDUF inserts N new blank records.
After invoking either command, you’re left with blank records to fill. To name a MultiPage, enter the name in the leftmost cell of its record. Set its position and size by entering numbers or appropriate formulas in the cells of the columns headed Left, Top, Width, and Height. For details, see the section, “Specifying control positions”.
Creating new Pages for MultiPage controls
To create pages for a MultiPage control, begin by selecting a cell on the FormSpec worksheet. Your selection tells MDUF where you want to create the Page or Pages. If you select a cell that lies within the record of a defined MultiPage control, then MDUF inserts the Page or Pages into that MultiPage control. If you select a cell that lies within the record of a defined Page, then MDUF inserts the new Page into the same MultiPage control that hosts the Page whose record contains the selected cell.
After you’ve selected the appropriate cell, invoke the MDUF ribbon command This Control>Insert>Pages… , which causes MDUF to initialize and show the mDialogBox “Insert Page(s)”. You then use that UserForm to specify the names of the Pages you want to create. By default, MDUF sets the captions of new Pages to be equal to the Names of the new pages. You can modify the caption of a Page by editing the Caption cell of its record.
MDUF offers two ways of specifying the names of the added Pages: Textual Specification and Digital Specification.
Textual Specification The mDialogBox “Insert Page(s)” contains a TextBox on its left, and a ListBox on its right. If the MultiPage in question already has some Pages, they’re listed in the ListBox to the right. To add Pages to the MultiPage, enter their names in the TextBox to the left, one page name to a line, in the order in which you want to add them. To enter a newline in the TextBox, press Shift+Return (or Shift+Enter).
To determine the placement of your new Pages among the existing Pages of the MultiPage, use the ListBox. If you select one of the Pages listed there, MDUF enters your new Pages before (that is, above) the Page you selected. If you select “After last page”, the new Pages are inserted after the last existing Page.
If the MultiPage in question contained no pages when you invoked this command, the ListBox contains a note to that effect. If this is unexpected, click Cancel and investigate.
When you click OK, MDUF inserts the Pages you specified into the range of Page records.
Digital Specification In some applications, the names of the Pages of a MultiPage are of little use. For this case, you can ask MDUF to create names for the Pages you’re adding. The names it creates are pagPage0, pagPage1, pagPage2, and so on. You can invoke this capability as follows.
As noted above, the mDialogBox “Insert Page(s)” has a TextBox in its left half. If you enter a non-negative integer into that TextBox, MDUF creates Pages from 0 up to and including that integer. For example, if you enter 3, the created Pages are named pagPage0, pagPage1, pagPage2, and pagPage3.
There are two limitations in this approach.
First, you cannot control the placement of these Pages among the existing Pages of the MultiPage, if any. Pages specified this way are always placed last in the collection of Pages of the MultiPage.
Second, the newly created Pages must “follow” all the existing Pages. That is, if Pages with index 0-4 already exist, then if you enter 6 into the UserForm TextBox, MDUF creates only Pages named pagPage5 and pagPage6. If Pages with index 0-4 already exist, and you enter a number 4 or smaller, no Pages are created. Of course, if no Pages exist, all Pages with index from 0 through 6 would be created.
A final note about Digital specification: After you create Pages using Digital Specification, you can use the MDUF ribbon command This Control>Rename… to rename the pages you created.
Deleting a Page from a MultiPage
To delete a Page from a MultiPage, first select any cell in its record on the FormSpec worksheet. Next, invoke the MDUF ribbon command This FormSpec>Delete>Selected Controls. MDUF then deletes the selected Page and all controls it hosts. If your selection contains cells belonging to the records of multiple Pages, MDUF deletes all those Pages, and the controls they host, from the FormSpec worksheet. When you next generate the UserForm, the deleted Pages will be gone.
Note: When you delete a Page or Pages from the FormSpec worksheet, MDUF re-indexes the remaining Pages to ensure that the set of indices for the Pages of a MultiPage is complete and well ordered. For this reason, when you write back-end code to meet the needs of UserForms containing MultiPages, it’s safest to reference Pages by name. Referencing them by index could potentially create a need to manually edit the back-end code after you alter the Page contents of a MultiPage.
Reordering the Pages of a MultiPage
To change the order of the Pages of a MultiPage, begin by selecting a cell in the record of the MultiPage whose Pages you want to reorder. Then invoke the MDUF ribbon command This Control>Reorder Pages…. MDUF then presents the mDialogBox titled “Reorder Pages”. This mDialogBox contains a ListBox that displays the current order of the Pages of the MultiPage. The prompt at the top of the mDialogBox contains the name of that MultiPage. This helps you to verify that you actually are working on the MultiPage you intend to adjust.
Next, in that ListBox, select a page that you want to move up or down in the Page order. Clicking the command button labeled “Move Up” moves the selected Page up; clicking “Move Down” moves it down. “Up” means “to the left” in the row of tabs in the MultiPage of your UserForm; “Down” means “to the right.” When you have the order you want, click OK to save it and close the mDialogBox. Nothing changes in the FormSpec worksheet until you click OK. Clicking Cancel at any time exits the procedure without making any changes.
To restore the ListBox contents to its initial Page order, click Reset. This is handy if you’ve made some changes (but have not yet saved them) and you want to start over again.
Setting the BackColor of a Page
Excel for Mac does not support setting the backcolor of a Page of a MultiPage. You can achieve this effect, however, by adding a Frame control to each Page of the MultiPage and setting its BackColor. In a future release of MDUF this approach might be automated, driven by specifying a “pseudo-backcolor” for the Page.
Specifying controls on a Page
In MDUF, all controls with the exception of Page have a Page property. If a control is hosted directly on a UserForm, its Page property is empty. But if the control is hosted on a Page of a MultiPage, the Page property value specifies the name of the host Page.
For example, to specify that MultiPage Foo hosts a control on a Page named Page1, you would enter in the Page slot of the control the string Foo[Page1] .
Specifying Pages
Every Page of an Excel UserForm must be specified on the MDUF FormSpec worksheet. The properties of the MDUF Page are identical to the properties of the corresponding Excel Page, with four exceptions:
- The MDUF Page has an Mpg property that holds the name of the MultiPage that hosts the Page.
- The Page in Excel for Mac (and therefore in MDUF) has a VerticalScrollBarSide property that Excel for a Windows Page does not.
- The Page in Excel for Windows has an Accelerator property that a Page in Excel for Mac (and therefore MDUF) does not.
- The Name property of each Page of a given MultiPage is unique within that MultiPage in MDUF. That is, in MDUF, no two Pages of a given MultiPage can have the same name. See “An MDUF constraint on Page names”.
Specifying Page properties
There are two sets of Page properties on the FormSpec worksheet, shown in the table below. To edit properties shown in the column headed Worksheet edit the corresponding cell on the FormSpec worksheet. To edit the properties shown in the column headed Properties Editor use the Properties Editor.
| Worksheet | Properties Editor |
| Name | ControlTipText |
| Mpg | Cycle |
| Index | Enabled |
| Caption | PictureAlignment |
| TransitionEffect | PictureSizeMode |
| TransitionPeriod | PictureTiling |
| ScrollBars | Tag |
| KeepScrollBarsVisible | Visible |
| ScrollHeight | |
| VerticalScrollBarSide | |
| ScrollLeft | |
| ScrollTop | |
| ScrollWidth | |
| Zoom |
Specifying control positions on Pages
When you invoke control-position macro for a control hosted on a Page of a MultiPage, it computes its result relative to the edges of the Page, not the edges of the MultiPage or the edges of the Form.
For the most part then, you specify the positions of controls on Pages just as you would specify their positions in the UserForm proper. There are four exceptions: FlushRight, FlushBottom, VMiddle, and HCenter. To invoke these worksheet functions for controls that are hosted on Pages of a MultiPage, you must supply an argument that specifies the MultiPage in question. Specifically, that argument must be the name of the MultiPage, or a formula that evaluates to the name of that MultiPage. Here’s why.
When these worksheet functions make their computations, they need to know either the Width or the Height of the space in which they are making their computations. They can deduce those quantities if they know the identity of the MultiPage that owns the Page on which the control is to reside. The other position-setting worksheet functions all operate relative to other control positions, or relative to the Top or Left of the space. They don’t need the Height or Width information to do their jobs. So we must accommodate the needs of these four functions—FlushRight, FlushBottom, VMiddle, and HCenter—by providing the identity of the MultiPage as an argument.
Page procedures: New Page, Delete Page, Rename… , and Move…
In earlier Excel for Mac, and in Excel for Windows, there is a shortcut menu for the page tabs. It has four items: New Page, Delete Page, Rename… , and Move… . The menu is available only in Designer View. In current Excel for Mac, there is no Designer view, and therefore this menu isn’t available. These procedures must be performed in MDUF using ribbon commands or by editing the FormSpec worksheet. These commands are documented elsewhere in this guide. Follow these links: