Working with Excel 2016 Dialog Box Controls in VBA - dummies

Working with Excel 2016 Dialog Box Controls in VBA

By John Walkenbach

There are many ways you can work with dialog box controls in VBA Excel 2016. So if you are ready for this step, keep reading. Here, you find out how to work with dialog box controls in a UserForm object.

Moving and resizing controls

After you place a control in a dialog box, you can move it and resize it by using standard mouse techniques. Or for precise control, you can use the Properties window to enter a value for the control’s Height, Width, Left, or Top property.

You can select multiple controls by Ctrl+clicking the controls. Or you can click and drag to “lasso” a group of controls. When multiple controls are selected, the Properties window displays only the properties common to all selected controls. You can change those common properties, and the change will be made to all controls you select which is much quicker than doing them one at a time.

A control can hide another control; in other words, you can stack one control on top of another. Unless you have a good reason for doing so, make sure that you do not overlap controls.

Aligning and spacing controls

The Format menu in the VBE window provides several commands to help you precisely align and space the controls in a dialog box. Before you use these commands, select the controls you want to work with. These commands work just as you might expect. Here is a dialog box with several CheckBox controls that are about to be aligned.

Choose the Format  -->  Align command to change the alignment of UserFor
Choose the Format  –>  Align command to change the alignment of UserForm controls.

When you select multiple controls, the last selected control appears with white handles rather than the normal black handles. The control with the white handles is the basis for aligning or resizing the other selected controls when you use the Format menu.

Accommodating keyboard users

Many users prefer to navigate through a dialog box by using the keyboard: Pressing Tab or Shift+Tab cycles through the controls, while pressing a hot key instantly activates a particular control.

To make sure that your dialog box works properly for keyboard users, you must be mindful of two issues:

  • Tab order

  • Accelerator keys

Changing the tab order

The tab order determines the order in which the controls are activated when the user presses Tab or Shift+Tab. It also determines which control has the initial focus — that is, which control is the active control when the dialog box first appears. For example, if a user is entering text in a TextBox, the TextBox has the focus. If the user clicks an OptionButton, the OptionButton has the focus. The first control in the tab order has the focus when Excel first displays a dialog box.

To set the control tab order, choose View  →  Tab Order. You can also right-click the dialog box and choose Tab Order from the shortcut menu. In either case, Excel displays the Tab Order dialog box.

The Tab Order dialog box.
The Tab Order dialog box.

The Tab Order dialog box lists all the controls in the UserForm. The tab order in the UserForm corresponds to the order of the items in the list. To change the tab order of a control, select it in the list and then click the Move Up or Move Down button. You can choose more than one control (click while pressing Shift or Ctrl) and move them all at one time.

Rather than use the Tab Order dialog box, you can set a control’s position in the tab order by using the Properties window. The first control in the tab order has a TabIndex property of 0. If you want to remove a control from the tab order, set its TabStop property to False.

Some controls (such as Frame or MultiPage controls) act as containers for other controls. The controls inside a container control have their own tab order. To set the tab order for a group of OptionButtons inside a Frame control, select the Frame control before you choose the View  →  Tab Order command.

Setting hot keys

Normally, you want to assign an accelerator key, or hot key, to dialog box controls. You do so by entering a letter for the Accelerator property in the Properties window. If a control doesn’t have an Accelerator property (a TextBox, for example), you can still allow direct keyboard access to it by using a Label control. That is, assign an accelerator key to the Label and put the Label directly before the TextBox in the tab order.

Here is a UserForm with three TextBoxes. The Labels that describe the TextBoxes have accelerator keys, and each Label precedes its corresponding TextBox in the tab order. Pressing Alt+D, for example, activates the TextBox next to the Department Label.

Use Labels to provide direct access to controls that don’t have accelerator keys.
Use Labels to provide direct access to controls that don’t have accelerator keys.

Testing a UserForm

The VBE offers three ways to test a UserForm without calling it from a VBA procedure:

  • Choose the Run  →  Run Sub/UserForm command.

  • Press F5.

  • Click the Run Sub/UserForm button on the Standard toolbar.

When a dialog box is displayed in this test mode, you can try out the tab order and the accelerator keys.