Using Dialog Box Controls in Excel VBA

In This Chapter

Understanding each type of dialog box control Changing each control’s properties Working with dialog box controls user responds to a custom dialog box (also known as a UserForm) by using the various controls (buttons, edit boxes, option buttons, and so on) that the dialog box contains. Your VBA code then makes use of these responses to determine which actions to take. You have lots of controls at your disposal, and this chapter tells you about them.
If you worked through the hands-on example in Chapter 16, you already have some experience with UserForm controls. This chapter fills in the gaps.

Getting Started with Dialog Box Controls

In this section, I tell you how to add controls to a UserForm, give them meaningful names, and adjust some of their properties.
Before you can do any of these things, you must have a UserForm, which you get by choosing InsertOUserForm in the VBE. When you add a UserForm, make sure that the correct project is selected in the Project window (if more than one project is available).

Adding controls

Oddly enough, the VBE doesn’t have menu commands that let you add controls to a dialog box. You must use the Toolbox, which I describe in the preceding chapter, to add controls. Normally, the Toolbox pops up automatically when you activate a UserForm in the VBE. If it doesn’t, you can display the Toolbox by choosing ViewOToolbox.


Follow along to add a control to the UserForm:

1. Click the Toolbox tool that corresponds to the control you want to add.
2. Click in the UserForm.
3. Drag the control into position.
Alternatively, you can simply drag a control from the Toolbox to the UserForm to create a control with the default dimensions. Figure 17-1 shows a UserForm that contains a few controls.
A UserForm with a few controls added.
Figure 17-1:
A UserForm with a few controls added.
A UserForm may contain vertical and horizontal grid lines, which help align the controls you add. When you add or move a control, it snaps to the grid. If you don’t like this feature, you can turn off the grids:
1. Choose ToolsOOptions in the VBE.
2. In the Options dialog box, select the General tab.
3. Set your desired options in the Form Grid Settings section.

Introducing control properties

Every control that you add to a UserForm has properties that determine how the control looks and behaves. You can change a control’s properties at these two times:
At design time — when you’re designing the UserForm. You do so manually, using the Properties window.
At run time — while your program is running. You do so by writing VBA code.
When you add a control to a UserForm, you almost always need to make some design-time adjustments to its properties. You make these changes in the
Properties window. (To display the Properties window, press F4.) Figure 17-2 shows the Properties window, which displays properties for the object selected in the UserForm.
Use the Properties window to make design-time changes to a control's properties.
Figure 17-2:
Use the Properties window to make design-time changes to a control’s properties.
To change a control’s properties at run time you must write VBA code. For example, you may want to hide a particular control when a check box is checked by the user. In such a case, you would write code to change the control’s Visible property.
Each control has its own set of properties. All controls, however, share some common properties, such as Name, Width, and Value. Table 17-1 lists some of the common properties available for most controls.

Table 17-1 Common Control Properties
Property What It Does
Accelerator The letter underlined in the control’s caption. The user
presses this key in conjunction with the Alt key to select
the control.
AutoSize If True, the control resizes itself automatically based on
the text in its caption.
BackColor The control’s background color.
BackStyle The background style (transparent or opaque).
Caption The text that appears on the control.
Value The control’s value.
Table 17-1 (continued)
Property What It Does
Left and Top Values that determine the control’s position.
Width and Height Values that determine the control’s width and height.
Visible If False, the control is hidden.
Name The control’s name. By default, a control’s name is based
on the control type. You can change the name to any valid
name, but each control’s name must be unique within the
dialog box.
Picture A graphics image to display. The image must be contained
in a file; it can’t be copied from the Clipboard.

When you select a control, that control’s properties appear in the Properties window. To change a property, just select it in the Properties window and make the change. Some properties give you some help. For example, if you need to change the TextAlign property, the Properties window displays a drop-down list that contains all valid property values as shown in Figure 17-3.
Change properties by selecting from a dropdown list of valid property values.
Figure 17-3:
Change properties by selecting from a dropdown list of valid property values.

Dialog Box Controls — the Details

In the following sections I introduce you to each type of control you can use in custom dialog boxes and discuss some of the more useful properties. I don’t discuss every property for every control because that would take up too much space (and would be very boring).
The Help system for controls and properties is thorough. To find complete details for a particular property, select the property in the Properties window and press Fl. Figure 17-4 shows part of the online help for the ControlSource property.
The Help system provides lots of information for each property and control.
Figure 17-4:
The Help system provides lots of information for each property and control.
All of the sample files in this section are available at this topic’s Web site.

CheckBox control

A CheckBox control is useful for getting a binary choice: yes or no, true or false, on or off, and so on. Figure 17-5 shows some examples of CheckBox controls.
CheckBox controls.
Figure 17-5:
CheckBox controls.

The following is a description of a CheckBox control’s most useful properties:

Accelerator: A letter that lets the user change the value of the control by using the keyboard. For example, if the accelerator is A, pressing Alt+A changes the value of the CheckBox control (from checked to unchecked, or from unchecked to checked).
ControlSource: The address of a worksheet cell that’s linked to the CheckBox. The cell displays TRUE if the control is checked or FALSE if the control is not checked.
Value: If True, the CheckBox has a checkmark. If False, it does not have a checkmark.

ComboBox control

A ComboBox control is similar to a ListBox control (described later, in this chapter’s “ListBox control” section). A ComboBox, however, is a drop-down box and displays only one item at a time. Another difference is that the user may be allowed to enter a value that does not appear in the list of items. Figure 17-6 shows two ComboBox controls.
ComboBox controls.
Figure 17-6:
ComboBox controls.

The following is a description of some useful ComboBox control properties:

BoundColumn: If the list contains multiple columns, this property determines which column contains the returned value.
ColumnCount: The number of columns in the list.
ControlSource: A cell that stores the value selected in the ComboBox.
ListRows: The number of items to display when the list drops down.
ListStyle: The appearance of the list items.
RowSource: A range address that contains the list of items displayed in the ComboBox.
Style: Determines whether the control acts like a drop-down list or a combo box. A drop-down list doesn’t allow the user to enter a new value.
Value: The text of the item selected by the user and displayed in the ComboBox.
If your list of items is not in a worksheet, you can add items to a ComboBox control by using the AddItem method. More information on this method is provided in Chapter 18.

CommandButton control

CommandButton is simply a clickable button. It is of no use unless you provide an event-handler procedure to execute when the button is clicked. Figure 17-7 shows a dialog box with a few CommandButtons. One of these buttons features a picture (specified using the Picture property).
Command Button controls.
Figure 17-7:
Command Button controls.
When a CommandButton is clicked, it executes a macro with a name that consists of the CommandButton’s name, an underscore, and the word Click. For example, if a command button is named MyButton, clicking it executes the macro named MyButton_Click. This macro is stored in the Code window for the UserForm.

The following is a description of some useful CommandButton control properties:

Cancel: If True, pressing Esc executes the macro attached to the button. ‘ Default: If True, pressing Enter executes the macro attached to the button.

Frame control

A Frame control encloses other controls. You do so either for aesthetic purposes or to logically group a set of controls. A frame is particularly useful when the dialog box contains more than one set of OptionButton controls. (See “OptionButton control,” later in this chapter.)

The following list describes some useful Frame control properties:

BorderStyle: The frame’s appearance.
Caption: The text displayed at the top of the frame. The caption can be an empty string if you don’t want the control to display a caption.

Image control

An Image control displays an image. You may want to use an Image control to display your company’s logo in a dialog box. Figure 17-8 shows a dialog box with an Image control that displays a photo of a famous Excel topic author.

The following list describes the most useful Image control properties:

Picture: The graphics image that is displayed.
PictureSizeMode: How the picture is displayed if the control size does not match the image size.
When you click the Picture property, you are prompted for a filename. However, the graphics image is stored in the workbook. That way, if you distribute your workbook to someone else, you don’t have to include a copy of the graphics file.
Here’s an easy way to set the Picture property: Copy the image to the Clipboard, select the Image property in the Properties box, and press Ctrl+V to paste the copied image.
Some graphics images are very large and can make your workbook size increase dramatically. For best results, use an image that’s as small as possible.

Label control

A Label control simply displays text in your dialog box. Figure 17-9 shows a few Label controls. As you can see, you have a great deal of influence over the formatting of a Label control.
Label controls are easily molded.
Figure 17-9:
Label controls are easily molded.

ListBox control

The ListBox control presents a list of items from which the user can choose one or more. Figure 17-10 shows a dialog box with two ListBox controls.
ListBox controls.
Figure 17-10:
ListBox controls.
ListBox controls are very flexible. For example, you can specify a worksheet range that holds the ListBox items, and the range can consist of multiple columns. Or you can fill the ListBox with items by using VBA code.

The following is a description of the most useful ListBox control properties:

BoundColumn: If the list contains multiple columns, this property determines which column contains the returned value.
ColumnCount: The number of columns in the list.
ControlSource: A cell that stores the value selected in the ListBox.
IntegralHeight: This is True if the ListBox height adjusts automatically to display full lines of text when the list is scrolled vertically. If False, the ListBox may display partial lines of text when it is scrolled vertically.
ListStyle: The appearance of the list items.
MultiSelect: Determines whether the user can select multiple items from
the list.
RowSource: A range address that contains the list of items displayed in the ListBox.
Value: The text of the selected item in the ListBox.
If the ListBox has its MultiSelect property set to 1 or 2, then the user can select multiple items in the ListBox. In such a case, you cannot specify a ControlSource; you need to write a macro that determines which items are selected. Chapter 18 demonstrates how to do so.

MultiPage control

A MultiPage control lets you create tabbed dialog boxes, like the one that appears when you choose the ToolsOOptions command. Figure 17-11 shows an example of a custom dialog box that uses a MultiPage control. This particular control has three pages, or tabs.
Use a MultiPage control to create a tabbed dialog box.
Figure 17-11:
Use a MultiPage control to create a tabbed dialog box.

Descriptions of the most useful MultiPage control properties follow:

Style: Determines the appearance of the control. The tabs can appear normally (on the top), on the left, as buttons, or hidden (no tabs — your VBA code determines which page is displayed).
Value: Determines which page or tab is displayed. A Value of 0 displays the first page, a Value of 1 displays the second page, and so on.
By default, a MultiPage control has two pages. To add pages, right-click a tab and select New Page from the resulting Context menu.

OptionButton control

OptionButtons are useful when the user needs to select from a small number of items. OptionButtons are always used in groups of at least two. Figure 17-12 shows two sets of OptionButtons (Report Destination and Layout). One set uses graphics images (set with the Picture property).
Two sets of Option Button controls, each contained in a Frame control.
Figure 17-12:
Two sets of Option Button controls, each contained in a Frame control.

The following is a description of the most useful OptionButton control properties:

Accelerator: A letter that lets the user select the option by using the keyboard. For example, if the accelerator for an option button is C, then pressing Alt+C selects the control.
GroupName: A name that identifies an option button as being associated with other option buttons with the same GroupName property.
ControlSource: The worksheet cell that’s linked to the option button. The cell displays TRUE if the control is selected or FALSE if the control is not selected.
Value: If True, the OptionButton is selected. If False, the OptionButton is not selected.
If your dialog box contains more than one set of OptionButtons, you must change the GroupName property for all OptionButtons in a particular set. Otherwise, all OptionButtons become part of the same set. Alternatively, you can enclose each set of OptionButtons in a Frame control, which automatically groups the OptionButtons in the frame.

RefEdit control

The RefEdit control is used when you need to let the user select a range in a worksheet. Figure 17-13 shows a custom dialog box with two RefEdit controls. Its Value property holds the address of the selected range.
Two RefEdit controls.
Figure 17-13:
Two RefEdit controls.

ScrollBar control

The ScrollBar control is similar to a SpinButton control (described later). The difference is that the user can drag the ScrollBar’s button to change the control’s value in larger increments. Figure 17-14 shows a ScrollBar control. Its Value is displayed in a Label control.
A ScrollBar control, with a Label control below it.
Figure 17-14:
A ScrollBar control, with a Label control below it.

The following is a description of the most useful properties of a ScrollBar control:

Value: The control’s current value. Min: The control’s minimum value. Max: The control’s maximum value.
ControlSource: The worksheet cell that displays the control’s value.
SmallChange: The amount that the control’s value is changed by a click.
LargeChange: The amount that the control’s value is changed by clicking either side of the button.
The ScrollBar control is most useful for specifying a value that extends across a wide range of possible values.

SpinButton control

The SpinButton control lets the user select a value by clicking the control, which has two arrows (one to increase the value and the other to decrease the value). Figure 17-15 shows a dialog box that uses two SpinButton controls. Each control is linked to the Label control on the right (by using VBA procedures).
SpinButton controls.
Figure 17-15:
SpinButton controls.

The following descriptions explain the most useful properties of a SpinButton control:

Value: The control’s current value. Min: The control’s minimum value. Max: The control’s maximum value.
ControlSource: The worksheet cell that displays the control’s value.
SmallChange: The amount that the control’s value is changed by a click. Usually this property is set to 1, but you can make it any value.
If you use a ControlSource for a SpinButton, you should understand that the worksheet is recalculated every time the control’s value is changed. Therefore, if the user changes the value from 0 to 12, the worksheet is calculated 12 times. If your worksheet takes a long time to calculate, you may want to avoid using a ControlSource to store the value.

TabStrip control

A TabStrip control is similar to a MultiPage control, but it’s not as easy to use. In fact, I’m not sure why this control is even included. You can pretty much ignore it and use the MultiPage control instead.

TextBox control

A TextBox control lets the user enter text. Figure 17-16 shows a dialog box with two TextBox controls.
TextBox controls.
Figure 17-16:
TextBox controls.

The following is a description of the most useful TextBox control properties:

AutoSize: If True, the control adjusts its size automatically, depending on the amount of text.
ControlSource: The address of a cell that contains the text in the TextBox.
IntegralHeight: If True, the TextBox height adjusts automatically to display full lines of text when the list is scrolled vertically. If False, the TextBox may display partial lines of text when it is scrolled vertically.
MaxLength: The maximum number of characters allowed in the TextBox. If 0, the number of characters is unlimited.
MultiLine: If True, the TextBox can display more than one line of text.
TextAlign: Determines how the text is aligned in the TextBox.
WordWrap: Determines whether the control allows word wrap.
ScrollBars: Determines the type of scroll bars for the control: horizontal, vertical, both, or none.

ToggleButton control

A ToggleButton control has two states: on and off. Clicking the button toggles between these two states, and the button changes its appearance when clicked. Its value is either True (pressed) or False (not pressed). You can sometimes use a toggle button in place of a CheckBox control. Figure 17-17 shows a dialog box with some ToggleButton controls.
Toggle Button controls.
Figure 17-17:
Toggle Button controls.

Working with Dialog Box Controls

In this section, I discuss 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.
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 would expect, so I don’t explain them here. Figure 17-18 shows a dialog box with several CheckBox controls about to be aligned.
Use the Formato Align command to change the alignment of UserForm controls.
Figure 17-18:
Use the Formato 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 (including me) prefer to navigate through a dialog box by using the keyboard: Pressing Tab and 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 into 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 shown in Figure 17-19.
The Tab Order dialog box.
Figure 17-19:
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 move a control, select it and then click the arrow buttons up or down. 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 ViewOTab 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.
Figure 17-20 shows several 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.
Figure 17-20:
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 RunORun 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.

Dialog Box Aesthetics

Dialog boxes can look good, bad, or somewhere in between. A good-looking dialog box is easy on the eye, has nicely sized and aligned controls, and makes its function perfectly clear to the user. Bad-looking dialog boxes confuse the user, have misaligned controls, and give the impression that the developer didn’t have a plan (or a clue).
A good rule to follow is to try to make your dialog boxes look like the Excel built-in dialog boxes. As you gain more experience with dialog box construction, you can duplicate almost all the features of the Excel dialog boxes.

Next post:

Previous post: