When a UserForm is displayed, the TabIndex property of a control tells Excel how to change focus if the user presses the Tab key. A sensible order is reassuring to the user.
- How Excel’s TabIndex works in Excel for Mac
- Using the TabIndex and TabStop mDialogBox
- Clearing Tab data
- Navigating from one TabIndex to another
- Relative assignment of tab order
- Automated assignment of the TabIndex property
How Excel’s TabIndex works in Excel for Mac
The TabIndex property of a control tells Excel how to change focus when a UserForm is displayed and the user presses the Tab key. In an oversimplified example, if control N has TabIndex N, and the user presses the Tab key, the control with TabIndex N+1 gets focus. The TabIndex property of the first control in the tab order is 0; the TabIndex property of the second is 1, and so on.
This is a bit oversimplified, first because the tab order wraps around. When you press Tab and the last control in the order has focus, the first control gets focus. Second, it’s oversimplified because the TabStop property has something to say about this behavior. If the TabStop property of a control is True, then the control can receive focus; if False, then the control cannot receive focus.
So if a control X is in the tab order, but its TabStop property is false, then when the user presses Tab for the control that precedes X in the Tab order, focus shifts to the first control that has TabStop True and is later in the Tab order than control X. If there is no such control, the tab order wraps around to the beginning of the order.
Establishing a tab order for your UserForms is especially important on the Mac because unlike Windows, the Mac doesn’t support accelerator keys. You can define accelerator keys in MDUF, but your Mac users can’t employ them. Controlling tab order is the next best thing.
Using the TabIndex and TabStop mDialogBox
MDUF has an mDialogBox that helps you set the tab order of the controls on your UserForms. The TabIndex and TabStop mDialogBox sets both TabIndex and TabStop properties for the controls represented on a FormSpec worksheet. You invoke the TabIndex and TabStop mDialogBox by first activating the FormSpec worksheet for the UserForm whose TabIndex and TabStop properties you want to adjust. Then you invoke the MDUF ribbon command This FormSpec>This Form>Tab Data>Set Tab Order….
The mDialogBox has a single ListBox that displays the control names and TabStop values of all controls represented on the active FormSpec worksheet. The order of display is the TabIndex, increasing downward, with the first control at the top of the list. The mDialogBox offers the commands described below.
OK
This command saves the TabIndex and TabStop for each control listed in the mDialogBox, in the order displayed, and with the TabStop values displayed. Controls not listed are removed from the tab order, and are assigned a TabStop value of False.
Cancel
The mDialogBox is hidden and the FormSpec worksheet remains unchanged.
The following commands take action based on the controls you have selected in the mDialogBox’s ListBox.
Move Up; Move Down
Moves the selected controls up (or down) by one slot in the list. If multiple selected controls are adjacent in the list, they are treated as a unit. That is, if a block of three adjacent controls is selected, and they are moved down, they move to a position directly below the control immediately below them. The command is aborted if a selected control that is already in the top (bottom) position would be required to move above (below) the top (bottom) position.
Toggle TabStop
Flips the TabStop state for each of the selected controls. If a control has TabStop True, this command causes its TabStop to become False, and vice versa.
All TabStops True
Sets the TabStop property to True for all listed controls.
Remove Labels
Since labels cannot receive focus, there isn’t a reason for including them in the tab order. This command removes them from the TabIndex and TabStop UserForm. When you click OK, they acquire a TabStop property value of False, and they are removed from the tab order.
It’s probably a good idea to remove labels from the TabIndex and TabStop UserForm early in the process, because doing so reduces the effort required to move controls up or down.
Remove Selected
Removes the selected controls from the ListBox of the TabIndex and TabStop mDialogBox. When you click OK, the removed controls acquire a TabStop property value of False, and they are removed from the tab order.
Reset
Restores the mDialogBox state to the state that it had before you made any changes. This state corresponds to the state of the FormSpec worksheet at the time you invoked the command Display Tab Order Dialog…
Clearing Tab data
Three ribbon commands are available for removing Tab data from the active FormSpec worksheet.
Navigating from one TabIndex to another
After you’ve defined TabIndex for a number of controls, you might want to review the state of affairs. Because the FormSpec worksheet is organized by control type, rather than by TabIndex, you might find yourself chasing around a bit as you review the TabIndex values of the UserForm’s controls. Two keyboard commands make this kind of review a little less painful.
If the active cell contains the TabIndex of a control, then the keyboard command Cmd+Option+n (n for Next) (⌘⌥n) selects the cell containing the next higher TabIndex. Likewise, the keyboard command Cmd+Option+p (p for Previous) (⌘⌥p) selects the cell containing the next lower TabIndex.
If the active cell is empty, or if it does not lie in the TabIndex column of a FormSpec worksheet, Excel beeps. If MDUF is unable to find the TabIndex it’s looking for, it tells you so. Probably something is amiss, and working out what happened is up to you.
Relative assignment of tab order
MDUF provides a worksheet function TabAfter, which computes a control’s TabIndex. It takes a single argument. That argument is anything that evaluates to an integer, or to the name of the predecessor control (the control that immediately precedes in tab order the control whose TabIndex you’re computing). The argument to TabAfter can also be the registration ID of the predecessor control. Use this worksheet function in the control’s TabIndex cell.
So, for example, if the Cancel Button is to be next in tab order after the OK button (named “cmdOK”), and if their TabIndex cells are N27 and N28 respectively, you would enter into N28 the formula =TabAfter(“cmdOK”), or possibly =TabAfter(N27). Both produce the desired result.
Caution: If you invoke the ribbon command
Automated assignment of the TabIndex property
For complex UserForms, those with more than 10 or 15 controls, assigning TabIndex property values for all controls can be laborious. And maintenance of the tab order can become troublesome as well. The MDUF ribbon command This FormSpec>This Form>Tab Data>Compute TabIndices can save some effort when the UserForm has a columnar layout.
A UserForm has a columnar layout when it’s possible to define horizontal distances that split the form’s controls into columns. For example, a UserForm that is 500 twips wide has two columns when all members of one subset of its controls have a Left property that is less than, say, 400, and the remaining controls have a Left property that is 400 or more.
UserForms with the most fundamental columnar layout have only a single column: the entire UserForm.
Columnar layouts have multiple advantages. Perhaps the most significant is their visual simplicity. But I’ll leave that to the psychologists. I do want to point out one simplifying factor from the perspective of maintenance. If you set the tab order to go down each column, and from column to column left to right, then a wonderful thing happens. If for some reason you need to move a control from the bottom of one column to the top of the next, or from the top of one column to the bottom of the previous column, the tab order doesn’t change. This holds true even if the moves you make cause further moves “downstream” or “upstream.” It isn’t a feature of MDUF; it’s a feature of columnar layouts.
If a UserForm has a columnar layout, we can think of the columns as being numbered increasing left to right. In that layout, MDUF can assign TabIndex values to all controls automatically. The TabIndex increases first by column number. Within each column, the TabIndex is assigned according to a sort order. The sort is first by increasing value of the Top property and next by increasing value of the Left property.
Auto TabIndex does not consider labels at all. They are excluded from the tab order. There is little lost with this constraint, because labels cannot receive focus in UserForms for Microsoft 365 for Mac.
Specifying the column boundaries
The FormSpec worksheet contains a block of parameters titled “Form Properties”. One of the cells in this block has a caption of “ColumnBoundaries”. This is the cell that holds the specification for the UserForm’s column widths needed by the command Compute TabIndices.
To specify the column widths, enter into this cell a sequence of strictly increasing positive integers separated by semicolons. Each integer represents the right-hand edge of a column. For example, the string 250;600;900 represents four columns. From left to right, the right-hand edge of the first column is at 250; the right-hand edge of the second column is at 600; the right-hand edge of the third column is at 900; and the right-hand edge of fourth column is at the right-hand edge of the UserForm. (You need not specify the right-hand edge of the rightmost column.)
If you invoke the command , and if the cell ColumnBoundaries is blank, MDUF assumes that the UserForm has a single column encompassing the entire UserForm.
Using computed TabIndices as an aid
For complex UserForms, you might still want to set tab order manually for some controls. The command can be an aid in this process. Use it to create a preliminary tab order assignment, and then make manual adjustments.