Chapter 17
In This Chapter
Understanding each type of dialog box control
Changing each control’s properties
Manipulating dialog box controls in your UserForm object
Auser 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 quite a few 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 some of the gaps.
In this section, I tell you how to add controls to a UserForm, give them meaningful names, and adjust some of their properties.
Oddly enough, the VBE doesn’t have menu commands that let you add controls to a dialog box. You must use the floating Toolbox (which I describe in Chapter 16) 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 View ⇒ Toolbox.
Follow along to add a control to the UserForm:
Click in the UserForm, and drag to size and position the control.
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: Two OptionButtons (inside a Frame), a ComboBox, a CheckBox, a ScrollBar, and a CommandButton.
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 the following two times:
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 — which happens to be a CheckBox control.
Each control has its own set of properties. All controls, however, have some common properties, such as Name, Width, and Height. Table 17-1 lists some of the common properties available for many controls.
Table 17-1 Common Control Properties
Property |
What It Affects |
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. |
Left and Top |
Values that determine the control’s position. |
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 can be from a graphics file, or you can select the Picture property and paste an image that you copied to the Clipboard. |
Value |
The control’s value. |
Visible |
If False, the control is hidden. |
Width and Height |
Values that determine the control’s width and height. |
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.
In the following sections, I introduce you to each type of control you can use in custom dialog boxes and discuss some of the most useful properties. I don’t discuss every property for every control because that would require a book that's about four times as thick (and it would be a very boring book).
All the examples in this section are available on this book’s website.
A CheckBox control is useful for getting a binary choice: yes or no, true or false, on or off, and so on. Figure 17-4 shows some examples of CheckBox controls.
Following are a CheckBox control’s most useful properties:
A ComboBox control is similar to a ListBox control (described later in the “ListBox control” section). A ComboBox, however, is a drop-down control. Another difference is that the user may be allowed to enter a value that does not appear in the list of items. Figure 17-5 shows two ComboBox controls. The control on the right (for the year) is being used, so it has dropped down to display its list of options.
Following are some useful ComboBox control properties:
CommandButton is just a common clickable button. It is of no use unless you provide an event-handler procedure to execute when the button is clicked. Figure 17-6 shows a dialog box with nine CommandButtons. Two of these buttons feature clip-art images, which are added by copying the clip art and then pasting it into the Picture field in the Properties window.
When a CommandButton is clicked, it executes an event-handler procedure with a name that consists of the CommandButton’s name, an underscore, and the word Click. For example, if a CommandButton is named MyButton, clicking it executes the macro named MyButton_Click. This macro is stored in the Code window for the UserForm.
Following are some useful CommandButton control properties:
A Frame control encloses other controls. You use it 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:
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-7 shows a dialog box with an Image control that displays a photo of a cute little kitten.
The following list describes the most useful Image control properties:
When you click the Picture property, you are prompted for a filename. However, the graphics image (after it's retrieved) 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.
A Label control simply displays text in your dialog box. Figure 17-8 shows a few Label controls. As you can see, you have a great deal of control of the formatting of a Label control.
The ListBox control presents a list of items from which the user can choose one or more. Figure 17-9 shows a dialog box with two 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.
If a ListBox isn't tall enough to display all the items in the list, a scroll bar appears so the user can scroll down to see more items.
The following list is a description of the most useful ListBox control properties:
A MultiPage control lets you create tabbed dialog boxes, like the Format Cells dialog box (the one that appears when you press Ctrl+1 in Excel). Figure 17-10 shows an example of a custom dialog box that uses a MultiPage control. This particular control has three pages, or tabs.
Descriptions of the most useful MultiPage control properties follow:
OptionButtons are useful when the user needs to choose from a small number of items. OptionButtons are always used in groups of at least two. Figure 17-11 shows two sets of OptionButtons, and each set is contained in a frame.
The following is a description of the most useful OptionButton control properties:
The RefEdit control is used when you need to let the user select a range in a worksheet. Figure 17-12 shows a UserForm with two RefEdit controls. Its Value property holds the address of the selected range (as a text string).
When you add a ScrollBar control, you can make it horizontal or vertical. 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. Another difference is that when you click the up button on a vertical ScrollBar, the value decreases — which is a bit counterintuitive. So a ScrollBar is not always a good substitute for a SpinButton.
Figure 17-13 shows a ScrollBar control with a horizontal orientation. Its Value property is displayed in a Label control placed below the ScrollBar control.
Following are the most useful properties of a ScrollBar control:
The ScrollBar control is most useful for specifying a value that extends across a wide range of possible values.
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). Like a ScrollBar control, a SpinButton can be oriented either horizontally or vertically. Figure 17-14 shows a dialog box that uses two vertically oriented SpinButton controls. Each control is linked to the Label control on the right (by using VBA procedures).
The following descriptions explain the most useful properties of a SpinButton 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.
A TextBox control lets the user enter text. Figure 17-15 shows a dialog box with two TextBox controls.
Following are the most useful TextBox control properties:
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). Figure 17-16 shows a dialog box with four ToggleButton controls. The top one is pressed.
I never, ever use ToggleButton controls. I prefer to use CheckBox controls.
In this section, I discuss how to work with dialog box controls in a UserForm object.
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.
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.
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, so I don’t explain them here. Figure 17-17 shows a dialog box with several CheckBox controls that are about to be aligned.
Many users (including yours truly) 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:
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, shown in Figure 17-18.
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.
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-19 shows 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.
The VBE offers three ways to test a UserForm without calling it from a VBA procedure:
When a dialog box is displayed in this test mode, you can try out the tab order and the accelerator keys.
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 its function is 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).
Try to limit the number of controls on your form. If you do need many controls (a rule of thumb: more than ten controls), consider using a MultiPage control to split the task the user has to do into logical (and smaller) steps.
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.