To change the tab order of controlsYou use the same process to set most properties for controls on worksheets, chart sheets, and dialog sheets. For more information, see "Setting Control Properties" and "Assigning Code to Controls" earlier in this chapter. For controls on a dialog sheet, you can also set the tab order and access key to control how the user accesses the controls. For buttons, you can set them to either dismiss or cancel the dialog box. For edit boxes, you can restrict the kind of data the user can enter.
There are several ways for a user to access a control in a dialog box (a process also called setting the focus to the control). One way is simply to click a control such as a button, edit box, or list box. Another way is to press Alt and then click the access key for the control (discussed in the next section).
You can also establish a tab order for the controls in a dialog box. When the user presses Tab, controls become active in the sequence, or tab order, you've established. Establishing a logical tab order is particularly useful for custom data-entry forms, because it minimizes the number of keystrokes it takes to get from one edit box to the next. You can set a tab order only for controls on a dialog sheet, not for controls on a worksheet or chart sheet.
To change the tab order of controls
1. Switch to the dialog sheet.
2. On the Tools menu, click Tab Order.
3. Click the item whose place in the tab order you want to change.
To select more than one control, hold down CTRL as you click the items.
4. Click one of the arrow buttons above or below Move.
An access key is the key you press in conjunction with the ALT key to access a control in Microsoft Excel. As with setting the tab order, defining access keys works only for controls on a dialog sheet. You can assign an access key to a label, command button, check box, option button, or group box. When you do this, a single character in the text associated with the control is underlined when the dialog box is displayed.
To set an access key for a control
1. Select the control.
2. On the Format menu, click Object.
3. Click the Control tab.
4. In the Accelerator box, type the key you want to use as the access key.
Note
If you want to create the effect of an access key for a control on a worksheet, you can develop a procedure associated with the OnKey event that runs when the user presses a certain key combination. Creating an OnKey procedure is discussed in Chapter 11, "Automatic Procedures and OnEvent Procedures."
When you create a new dialog sheet, Microsoft Excel adds an OK button and a Cancel button. When the user clicks the OK button, the default behavior is to dismiss the dialog box and cause the Show method to return True. For the Cancel button, the default behavior is to cancel the dialog box and return False. In addition, Visual Basic runs any procedures associated with the buttons. By default, only these two buttons dismiss and cancel the dialog box.
You can change this default behavior using either the Control tab in the Format Object dialog box or the DismissButton and CancelButton properties. You can set any number of buttons to dismiss the dialog box, but you can set only one button to cancel it. For more information, see "DismissButton property" and "CancelButton property" in Help.
Note
Notice the difference between the terms dismiss and cancel. When the dialog box is dismissed, the Show method returns True, and Microsoft Excel processes the changes made to the dialog box. When the dialog box is canceled, the Show method returns False, and Microsoft Excel ignores changes made to the dialog box.
By default, an edit box allows the user to enter any data type. You may want to restrict each edit box to a single data type (for example, if you want the user to enter a filename, you can specify that the entry must be a string). You can set this either by using the Control tab in the Format Object dialog box or by using the InputType property for the edit box. When the user dismisses the dialog box, Microsoft Excel checks the entries in any edit boxes with data validation turned on. If the contents of an edit box are invalid, Microsoft Excel displays a message and switches the focus to the edit box so that the user can supply a value of the correct data type. This process continues until every edit box contains valid data.