In this chapter, you'll start looking at Visual Basic for Applications’ tools for creating custom dialog boxes that interact with the user. The terms dialog box and form (or user form) are generally used interchangeably. Technically, a dialog box is a quite simple, small window, such as a message box or input box. Forms, generally, are larger windows featuring richer and more complex interaction with the user. These terms, though, are synonymous in common usage.
Dialog boxes and forms are among the most powerful and feature-packed elements of VBA. We will spend quite a bit of time in Chapters 14, “Creating Simple Custom Dialog Boxes,” and 15, “Creating Complex Forms,” exploring their uses as the primary communication path between users and procedures.
This chapter covers the most common and straightforward components (called controls) that you can put on a form. The next chapter shows you how to create more elaborate forms, such as those with tabbed pages and those that update themselves when the user clicks a control.
You'll sometimes want to use a form (another word for dialog box or window) when simpler methods of interacting with the user are inadequate. Sometimes, the limited buttons provided in message boxes are insufficient for getting needed information from the user. Similarly, the single text field available in an input box would be inadequate if you need the user to provide multiple data (name, address, phone number, and so on). In other words, sometimes you need the user to fill in a form.
You'll also want to use a custom dialog box for specialized input. For example, you might want to allow users to:
In other words, simple message boxes or input boxes cannot handle complex user input like that.
Custom dialog boxes can include the full range of interface elements (controls) that the user is probably familiar with from working with Windows applications. You can create custom dialog boxes that look and function almost exactly like the dialog boxes built into applications (such as the File Save dialog box). Or you can build even larger constructions that approach the sophistication of typical application windows.
You might find that you'll use forms often in your more sophisticated macros. For example, when the user starts a procedure, you can have the procedure display a form presenting options—such as choosing the files for the procedure to manipulate. The user's choices determine what the procedure will then do.
You could also create dialog boxes that VBA triggers in response to events in the computer system—for example, an event that runs at a specific time or runs when the user takes a specific action (such as creating, opening, or closing a document).
Making your own dialog boxes is not that hard, but it can be time-consuming if you're building a complicated form. Because creating forms is not the fastest programming job, you might want to consider any practical alternatives to using them.
You've already looked at message boxes and input boxes, which provide a simple alternative for some of the relatively easy tasks for which you might want to create a custom dialog box.
Also, some applications, such as Word and Excel, even let you use their built-in dialog boxes for your own purposes. If users are familiar with the application, they're probably familiar with these built-in dialog boxes and can immediately use them to perform standard actions—for example, to open or save files. These are called common dialog boxes. How to use common dialog boxes in your macros is demonstrated briefly in the sidebar titled “Control a For…Next
Loop with User Input via a Dialog Box” in Chapter 12, “Using Loops to Repeat Actions,” and more fully later in this chapter in the section titled “Using an Application's Built-In Dialog Boxes.”
If you want to employ a custom dialog box or window in VBA, you use a visual object called a user form. A user form (also sometimes just referred to as a form) is a blank sheet on which you can place controls, such as check boxes, buttons, and text boxes, to create a made-to-order dialog box.
As you'll see, a user form contains its own code page where you, the programmer, write code to manage the various controls in the form. You can attach code to any of the controls, or to the user form itself, and that code is stored in the Code window. You can work with that code as you would macro code. You can also run and test a user form as you would any macro (for example, by pressing F5 with the user form selected), and the VBA Editor will execute the code behind the form.
You can display a user form (a dialog box) for the user to interact with, and you can then retrieve information from the user form and manipulate it with VBA code. It's in this sense that code supporting a form is said to be behind a form. The user sees and interacts with a form, but behind the scenes you have written code to intelligently react to whatever the user might input.
Each user form is itself an object and can contain a number of other objects that you can manipulate individually.
For example, you could create a simple dialog box with two option buttons, an OK button, and a Cancel button. Each option button would be an object, the OK button would be a third object, and the Cancel button would be a fourth object. You could set properties individually for each object—such as the action to take when the Cancel button is clicked or the ScreenTip (also called a ToolTip) to display when the user moves the mouse pointer over each of the option buttons. (ToolTips help make the components of your form understandable for the user.) The point is to consider the components of a form—the controls you place on the form—as objects. This is another use of the concept of objects. Controls are visual objects, but like purely programmatic objects, controls have members such as properties.
You can specify most properties of an object either at design time (when you're creating the user form) or at runtime (while the code is executing, either before or after you display the user form). For example, consider the Value
property of a check-box control. Your code (or the user) can set it to True
to display the check box in its selected state or to False
to display the check box in its cleared state. You can set the Value
property three different ways:
The following sections explain the process of creating a dialog box. Later in this chapter, you'll find examples that step you through the process of creating a procedure and adding a dialog box to it.
It's possible to whip together a half-decent dialog box without much planning. Some programmers like to just “sketch” the user interface in a dialog box by dragging and dropping controls from the Toolbox onto the form, then positioning the controls so they look good and, finally, modifying their properties. This is the approach favored by improvisational people.
Other programmers prefer to adopt a more methodical approach and plan what they need to include in the dialog box before they start creating it. If you fall into this latter category, consider the intended purpose of the dialog box and list the elements it will need in order to achieve this goal. Then perhaps sketch on paper a rough diagram of the dialog box to get an approximate idea of where you'll want to locate each of the elements (the controls you place on the form).
Once you have a design in mind, the first step in creating a custom dialog box is to insert a user form in the appropriate template or document:
The Visual Basic Editor opens a new user form like that shown in Figure 14.1, named UserForm1
(or the next available number if the project already contains other user forms).
The Visual Basic Editor also displays the Toolbox. (If you've previously hidden the Toolbox while working on a user form, the Visual Basic Editor doesn't display it. Choose View ➢ Toolbox or click the Toolbox button on the far right of the Standard toolbar.)
VBA automatically inserts the user form in the Forms
object (the collection of forms) for the project. If the project you chose didn't already contain a Forms
collection, VBA adds one to contain the new user form. You'll see the Forms
object displayed in the Project Explorer.
The Visual Basic Editor displays a grid in each user form to help you place controls relative to the dialog box and to align controls relative to each other so they look neat instead of random.
I can't think why you would want to do this, but to switch off the display of this grid or to switch off the Visual Basic Editor's automatic alignment of controls to the grid, follow these steps:
Next, change the user form's name property from the default (UserForm1
) to a more descriptive name. The following steps show how to do this. (For advice on choosing names, refer to the sidebar “Naming Conventions in Visual Basic for Applications” in this chapter.)
Figure 14.3 shows the two pages of the Properties window: Alphabetic and Categorized.
You can expand a category by clicking the plus (+) sign beside it to display the properties it contains, and collapse it by clicking the resulting minus (–) sign.
The Categorized option is not, in my view, very helpful because many of the properties are simply too difficult to fit into categories that make any sense. The Caption
property, for example, is assigned to the Appearance category, but the (Name)
property is contained in the Misc. collection. The very existence of a “miscellaneous” category demonstrates that the categorization effort has broken down. I suggest you stick with the default Alphabetic option instead.
UserForm1
or UserForm2
) in the cell to the right of the Name cell (you can double-click the name to select it quickly).This name can be anything you want, with the standard VBA limitations:
This name has no restrictions beyond the constraints imposed by the length of the title bar. You can enter a name longer than will fit in the title bar, but VBA truncates it with an ellipsis at its maximum displayable length. As you type, the name appears in the user-form title bar as well, so it's easy to see what's an appropriate length—at least, for the current size of the user form.
Now that you've renamed the user form, you're ready to add controls to it from the Toolbox, shown in Figure 14.4. VBA automatically displays the Toolbox when a user form is active, but you can also display the Toolbox when no user form is active by choosing View ➢ Toolbox.
Here's what the buttons on the Toolbox do:
Alternatively, if you double-click a control (such as the check box), you trigger a technique that allows you to quickly add multiple versions of the same control repeatedly. (Every time you click in the form, a new check box is added to it—for example, while the Editor is in this state.) To stop this repetitive behavior, you click the Select Objects button.
Click one of the controls in the Toolbox to select it. Then click in the user form to insert the control on the form, as illustrated in Figure 14.5. VBA places the top-left corner of the control where you click. As you place a control, it snaps to the grid on the user form (unless you've turned off the Align Controls To Grid feature as described in “Choosing User-Form Grid Settings,” earlier in this chapter).
You can resize the control as desired by selecting it and then clicking and dragging one of the selection handles (the white squares) that appear around it, as shown in Figure 14.6. The mouse pointer changes to a double-arrow icon when you've correctly positioned it to drag. When you drag a corner handle, VBA resizes the control on both sides of the corner. When you drag the handle at the midpoint of one of the control's sides, VBA resizes the control only in that dimension. In either case, VBA displays a dotted outline indicating the size that the control will be when you release the mouse button.
To resize the user form itself, click its title bar, or click in any blank space in the form (anywhere outside a control). This selects the user form. Then click and drag one of the selection handles that appear around the form.
To delete a control, right-click it in the user form and choose Delete from the context menu. Alternatively, click it to select it and then press the Delete key or choose Edit ➢ Delete. Restore it by pressing Ctrl+Z.
Sometimes it's quite efficient to temporarily select several controls as a group in the Editor. This allows you to manipulate all the grouped controls as a unit. For example, if you want to change the font size of three text boxes, two option buttons, and four labels, just group them and change the font-size property in the Properties window only once. The whole group will have all their font sizes changed automatically. (This trick is not related to grouping controls within a Frame control as described earlier in this chapter in the section titled “Adding Controls to the User Form.”)
We'll explore this useful grouping technique later in this chapter in the section titled “Working with Groups of Controls.” For now, I'll just briefly introduce the concept.
To delete, move, resize, or change the properties of multiple controls at once, first select them into a group. You can then delete them all at once by using the methods just described. Or you can move, resize, or modify the properties of the group as a whole.
Here's how to group controls:
As with user forms, VBA automatically gives each control that you add to a form a default name consisting of the type of control plus a sequential number. When you add the first text box in a user form, VBA names it TextBox1
. When you add another text box, VBA names it TextBox2
, and so on. (Each control in a dialog box must have a unique name so that you can refer to it specifically in code.)
You'll usually want to change the controls’ default names to names that describe their purposes so you can remember what they do for the macro.
For example, if TextBox2
is used for entering the user's organization name, you might want to rename it txtOrganizationName
, txtOrgName
, txtO_Name
, or something similar.
To rename a control, follow these steps:
Name
property.To move a control, click anywhere in it to select it, and then drag it to where you want it to appear, as shown in Figure 14.7.
To move a selected control, move the mouse pointer over the selection border around it so that the mouse pointer turns into a four-headed arrow (as shown in Figure 14.8), and then click and drag the control to where you want it to appear.
Some controls—such as option buttons and check boxes—have built-in text captions to let the user understand their purposes. You can change these captions like this:
VBA displays the blinking insertion cursor and a faint dotted border around the text, as shown in Figure 14.9.
Caption
property in the Properties window.)The following sections discuss the key properties of the controls in the default Toolbox.
First, I'll explain the common properties used to manipulate many of the controls effectively. After that, I'll go through the controls one by one, listing the properties particular to each control.
If you're new to VBA and find this section heavy going, just skip it for the time being and return to it when you're creating code and need to reference information about the properties of the controls.
Table 14.1 lists the properties shared by all or most controls, grouped by category.
TABLE 14.1: Properties common to most or all controls
PROPERTY INFORMATION | APPLIES TO | EXPLANATION |
General Properties | ||
BoundValue |
All controls except Frame, Image, and Label | Contains the value of the control when the control receives the focus in the user form. |
HelpContextID |
All controls except Image and Label | Returns the context identifier of the Help file topic associated with the control. |
Name |
All controls | Contains the name for the control. |
Object |
All controls | Enables you to assign to a control a custom property or method that uses the same name as a standard property or method. |
Parent |
All controls | Returns the name of the user form that contains the control. |
Tag |
All controls | Used for assigning extra information to the control. This is rarely used. |
Value |
CheckBox, ComboBox, CommandButton, ListBox, MultiPage, OptionButton, ScrollBar, SpinButton, TabStrip, TextBox, ToggleButton | One of the most varied properties, Value specifies the current state or value of the control. A CheckBox, OptionButton, or ToggleButton can have an integer value of −1 (True ), indicating that the item is selected, or a value of 0 (False ), indicating that the item is cleared. A ScrollBar or SpinButton returns a value containing the current value in the control. A ComboBox or ListBox returns the currently selected row's (or rows’) BoundColumn value. A MultiPage returns an integer indicating the active page, and a TextBox returns the text in the text box. |
The value of a CommandButton is False because choosing the command button triggers a Click event. However, you can set the value of a CommandButton to True , which has the same effect as clicking it. In other words, the value property is similar to the value of a variable, but the property's possible values are highly specific to each control. | ||
Size and Position | ||
Height |
All controls | The height of the control, measured in points. |
LayoutEffect |
All controls except Image | Indicates whether a control was moved when the layout of the form was changed. |
Left |
All controls | The distance of the left border of the control in pixels from the left edge of the form or frame that contains it. |
OldHeight |
All controls | The previous height of the control, measured in pixels. |
OldLeft |
All controls | The previous position of the left border of the control, measured in pixels. |
OldTop |
All controls | The previous position of the top border of the control, measured in pixels. |
OldWidth |
All controls | The previous width of the control, measured in points. |
Top |
All controls | The distance of the top border of the control in pixels from the top edge of the form or frame that contains it. |
Width |
All controls | The width of the control, measured in points. |
Appearance | ||
Alignment |
CheckBox, OptionButton, ToggleButton | Specifies how the caption is aligned to the control. |
AutoSize |
CheckBox, ComboBox, CommandButton, Image, Label, OptionButton, TextBox, ToggleButton | A Boolean (True or False only) property that controls whether the object resizes itself automatically to accommodate its contents. The default setting is False , which means that the control doesn't automatically resize itself. |
BackColor |
All controls | The background color of the control. This property contains a number representing the color. |
BackStyle |
CheckBox, ComboBox, CommandButton, Frame, Image, Label, OptionButton, TextBox, ToggleButton | Specifies whether the background of the object is transparent (fmBackStyleTransparent ) or opaque (fmBackStyleOpaque , the default). You can see through a transparent control—anything behind it on the form will show through. You can use transparent controls to achieve interesting effects—for example, by placing a transparent command button on top of an image or another control. |
BorderColor |
ComboBox, Image, Label, TextBox, ListBox | Specifies the color of the control's border. You can choose a border color from the System drop-down list or the palette or enter BorderColor as an eight-digit integer value (such as 16711680 for mid-blue). VBA stores the BorderColor property as a hexadecimal value (for instance, 00FF0000). For BorderColor to take effect, BorderStyle must be set to fmBorderStyleSingle . |
BorderStyle |
ComboBox, Frame, Image, Label, ListBox, TextBox, UserForm | Specifies the style of border on the control or user form. Use BorderStyle with the BorderColor property to set the color of a border. |
Caption |
CheckBox, CommandButton, Label, OptionButton, ToggleButton | A text string containing the description that appears for a control—the text that appears in a label, on a command button or toggle button, or next to a check box or option button. |
Font ( object) |
All controls except Image, SpinButton, and ScrollBar | Font —an object rather than a property—controls the font in which the label for the object is displayed. For TextBox, ComboBox, and ListBox controls, Font controls the font in which the text in the control is displayed. |
ForeColor |
All controls except Image | The foreground color of the control (often the text on the control). This property contains a number representing the color. |
Locked |
CheckBox, ComboBox, CommandButton, ListBox, OptionButton, TextBox, ToggleButton | A Boolean property that specifies whether the user can change the control. When Locked is set to True , the user can't change the control, though the control can still receive the focus (that is, be selected) and trigger events. When Locked is False (the default value), the control is open for editing. |
MouseIcon |
All controls except MultiPage | Specifies the image to display when the user moves the mouse pointer over the control. To use the MouseIcon property, the MousePointer property must be set to 99 , fmMousePointerCustom . |
MousePointer |
All controls except MultiPage | Specifies the type of mouse pointer to display when the user moves the mouse pointer over the control. |
Picture |
CheckBox, CommandButton, Frame, Image, Label, OptionButton, Page, ToggleButton, UserForm | Specifies the picture to display on the control. By using the Picture property, you can add a picture to a normally text-based control, such as a command button. |
PicturePosition |
CheckBox, CommandButton, Label, OptionButton, ToggleButton | Specifies how the picture is aligned with its caption. |
SpecialEffect |
CheckBox, ComboBox, Frame, Image, Label, ListBox, OptionButton, TextBox, ToggleButton | Specifies the visual effect to use for the control. For a CheckBox, OptionButton, or ToggleButton, the visual effect can be flat (fmButtonEffectFlat ) or sunken (fmButtonEffectSunken ). For the other controls, the visual effect can be flat (fmSpecialEffectFlat ), raised (fmSpecialEffectRaised ), sunken (fmSpecialEffectSunken ), etched (fmSpecialEffectEtched ), or a bump (fmSpecialEffectBump ). |
Visible |
All controls | Indicates whether the control is visible; expressed as a Boolean value. |
WordWrap |
CheckBox, CommandButton, Label, OptionButton, TextBox, ToggleButton | A Boolean property that specifies whether the text in or on a control wraps at the end of a line. For most controls, WordWrap is set to True by default; you'll often want to change this property to False to prevent the text from wrapping inappropriately. If the control is a TextBox and its MultiLine property is set to True , VBA ignores the WordWrap property. |
Behavior | ||
Accelerator |
CheckBox, CommandButton, Label, OptionButton, Page, Tab, ToggleButton | The accelerator key (or access key, or mnemonic) for the control—the key the user presses (typically, in combination with Alt) to access the control. For example, in many dialog boxes, the user can access the Cancel button by pressing Alt+C. The accelerator key for a label applies to the next control in the tab order rather than to the label itself. The accelerator character must be one of the characters in the control's text caption, usually the first (the C in Cancel, for example). But if some other control is already using the first character, you must, of course, choose a different one. Once you specify an accelerator character, VBA automatically underlines that character in the caption to cue the user that they can press, for example, Alt+C to select the Cancel button. For additional information on tab order, see the section titled “Adjusting the Tab Order of a Form” later in this chapter. |
ControlSource |
CheckBox, ComboBox, ListBox, OptionButton, ScrollBar, SpinButton, TextBox, ToggleButton | The cell or field used to set or store the Value of the control. The default value is an empty string ("" ), indicating that there is no control source for the control. |
ControlTipText |
All controls | The text of the ScreenTip displayed when the user holds the mouse pointer over the control. The default value of ControlTipText is a blank string, which means that no ScreenTip is displayed. |
Enabled |
All controls | A Boolean value that determines whether the control can be accessed (either interactively or programmatically). |
TabIndex |
All controls except Image | The position of the control in the tab order of the user form, expressed as an integer from 0 (the first position) through the number of controls on the user form. |
TabStop |
All controls except Image and Label | A Boolean value establishing whether the user can select the control by pressing the Tab key. If TabStop is set to False , the user can select the control only with the mouse. The TabStop setting doesn't change the tab order of the dialog box. |
The Label control simply displays text on the screen. It's most often used to identify the purpose of another control, so you frequently see a Label control placed on a form to the left of a text box whose purpose the label describes. Use the Caption
property to type in the text that you want the label to display. Use the TextAlign
property as shown in Table 14.2 to align the text of the label with the borders of the Label control.
TABLE 14.2: TextAlign property values for the Label control
FMTEXTALIGN CONSTANT | VALUE | TEXT ALIGNMENT |
fmTextAlignLeft |
1 | With the left border of the control |
fmTextAlignCenter |
2 | Centered on the control's area |
fmTextAlignRight |
3 | With the right border of the control |
The TextBox is one of the most commonly used controls. Recall that it can be a single-line control (often employed to display a field the user must fill in). Single-line is the default. Or it can be a multiline control, for displaying lots of text, as in a diary program where users determine how many lines they want to write. Adjust this feature with the MultiLine
property, which can be set to True or False. Also, the defaults for a TextBox are a size of 8 pt. (too small usually) and a sans serif font called Tahoma
(sans serif type is generally thought more appropriate for headlines than body text). So, you'll usually find yourself employing the Font
property to choose a larger font size and more readable font (such as Times New Roman).
Table 14.3 lists the key properties of the TextBox control.
TABLE 14.3: Key properties of the TextBox control
PROPERTY | DESCRIPTION |
AutoTab |
A Boolean property that determines whether VBA automatically moves to the next field when the user has entered the maximum number of characters in the text box or combo box. |
AutoWordSelect |
A Boolean property that determines whether VBA automatically selects a whole word when the user drags the mouse through text in a text box or a combo box. |
DragBehavior |
Enables or disables drag-and-drop for a text box or combo box: fmDragBehaviorDisabled (0 ) disables drag-and-drop; fmDragBehaviorEnabled (1 ) enables drag-and-drop. |
EnterFieldBehavior |
Determines whether VBA selects the contents of the edit area of the text box or combo box when the user moves the focus to the text box or combo box: fmEnterFieldBehaviorSelectAll (0 ) selects the contents of the text box or current row of the combo box; fmEnterFieldBehaviorRecallSelection (1 ) doesn't change the previous selection. |
EnterKeyBehavior |
A Boolean property that determines what VBA does when the user presses Enter with the focus on a text box. If EnterKeyBehavior is True , VBA creates a new line when the user presses Enter; if EnterKeyBehavior is False , VBA moves the focus to the next control on the user form. If MultiLine is False , VBA ignores the EnterKeyBehavior setting. |
HideSelection |
A Boolean property that determines whether VBA displays any selected text in a text box or combo box. If HideSelection is True , VBA displays the text without indicating the selection when the control doesn't have the focus. If HideSelection is False , VBA indicates the selection both when the control has the focus and when it doesn't. |
IMEMode |
Determines the default runtime mode of the Input Method Editor (IME). This property is used only in Far Eastern applications (for example, those using Japanese hiragana or katakana or Korean hangul). |
IntegralHeight |
A Boolean property that determines whether a list box or a text box resizes itself vertically to display any rows that are too tall to fit into it at its current height (True ) or not (False ). |
MultiLine |
A Boolean property that determines whether the text box can contain multiple lines of text (True ) or only one line (False ). When MultiLine is True , the text box adds a vertical scroll bar when the content becomes more than will fit within the current dimensions of the text box. VBA defaults to Multiline = False . |
PasswordChar |
Specifies the placeholder character to display in place of the characters the user types (so somebody peeping won't see the actual password). The common password character is the asterisk (*). This property is normally used for entering passwords and other information that needs to be obscured so that it cannot be read. |
ScrollBars |
Specifies which scroll bars to display on the text box. Usually, you'll do best to set the WordWrap property to True and let VBA add the vertical scroll bar to the text box as needed rather than using the ScrollBars property. |
SelectionMargin |
A Boolean property that determines whether the user can select a line of text in the text box or combo box by clicking in the selection bar to the left of the line. |
ShowDropButtonWhen |
Determines when to display the drop-down button for a combo box or a text box. fmShowDropButtonWhenNever (0 ) never displays the drop-down button and is the default for a text box. fmShowDropButtonWhenFocus (1 ) displays the drop-down button when the text box or combo box has the focus. fmShowDropButtonWhenAlways (2 ) always displays the drop-down button and is the default for a combo box. |
TabKeyBehavior |
A Boolean property that specifies whether the user can enter tabs in the text box. If TabKeyBehavior is True and MultiLine is True , pressing Tab enters a tab in the text box. If MultiLine is False , VBA ignores a TabKeyBehavior setting of True . If TabKeyBehavior is False , pressing Tab moves the focus to the next control in the tab order. |
These controls are similar. From the user's point of view, a key distinction is that a list box simply provides a list of options the user can choose from, whereas a combo box offers that list and also includes a field where the user can type in items.
Table 14.4 shows the key properties of the ComboBox control and the ListBox control. These two controls are similar and share many properties. They do, however, differ somewhat in behavior and features. These differences are described in the entries marked “List box only” and “Combo box only” in the table.
TABLE 14.4: Key properties of the ComboBox and ListBox controls
PROPERTY | DESCRIPTION |
AutoTab |
See Table 14.3. |
AutoWordSelect |
See Table 14.3. |
BoundColumn |
A Variant property that determines the source of data in a combo box or a list box that has multiple columns. The default setting is 1 (the first column). To assign another column, specify the number of the column (columns are numbered from 1, the leftmost column). To assign the value of ListIndex to BoundColumn , use 0 . |
ColumnCount |
A Long (data type) property that sets or returns the number of columns displayed in the combo box or list box. If the data source is unbound, you can specify up to 10 columns. To display all available columns in the data source, set ColumnCount to –1 . |
ColumnHeads |
A Boolean property that determines whether the combo box or list box displays headings on the columns (True ) or not (False ). |
ColumnWidths |
A String (data type) property that sets or returns the width of each column in a multicolumn combo box or list box. |
ListRows |
(Combo box only.) A Long (data type) property that sets or returns the number of rows displayed in the combo box. If the number of items in the list is greater than the value of ListRows , the combo box displays a scroll bar so that the user can scroll to the unseen items. |
ListStyle |
Determines the visual effect the list uses. For both a combo box and a list box, fmListStylePlain displays a regular, unadorned list. For a combo box, fmListStyleOption displays an option button to the left of each entry, allowing the user to select one item from the list. For a list box, fmListStyleOption displays option buttons for a single-select list and check boxes for a multiselect list. |
ListWidth |
(Combo box only.) A Variant property that sets or returns the width of the list in a combo box. The default value is 0 , which makes the list the same width as the text area of the combo box. |
MatchEntry |
Determines which type of matching the combo box or list box uses when the user types characters with the focus on the combo box or list box. fmMatch EntryFirstLetter (0 ) matches the next entry that starts with the letter or character typed: If the user types t twice, VBA selects the first entry beginning with t and then the second entry beginning with t. fmMatchEntryComplete (1 ) matches each letter the user types: If the user types te, VBA selects the entry that starts with te. fmEntryMatchNone (2 ) specifies no matching: The user can't select an item by typing in the list box or combo box but must use the mouse or the arrow keys instead. The default MatchEntry setting for a combo box is fmMatchEntryComplete . The default setting for a list box is fmMatchEntryFirstLetter . |
MatchRequired |
(Combo box only.) A Boolean property determining whether the user must select an entry from the combo box before leaving the control (True ) or not (False ). This property is useful for making sure that if the user types a partial entry into the text-box area of the combo box, they don't forget to complete the selection in the drop-down list area. If MatchRequired is True and the user tries to leave the combo box without making a selection, VBA displays an “Invalid Property Value” message box. |
MultiSelect |
(List box only.) Controls whether the user can make a single selection in the list or multiple selections. fmMultiSelectSingle (0 ) lets the user select only one item. fmMultiSelectMulti (1 ) lets the user select multiple items by clicking with the mouse or by pressing the spacebar. fmMultiSelectExtended (2 ) lets the user use Shift+click, Ctrl+click, and Shift with the arrow keys to extend or reduce the selection. |
RowSource |
A String property that specifies the source of a list to be displayed in a combo box or a list box. |
SelectionMargin |
See Table 14.3. |
ShowDropButtonWhen |
See Table 14.3. |
Check boxes are similar to option buttons—a set of choices presented to the user. However, option buttons permit the user to select only one from among the displayed options (like a set of radio pushbuttons). By contrast, users can select as many check boxes as they want.
Most of the properties of the CheckBox control have been discussed already. The key property of the CheckBox that you haven't come across yet is TripleState
, which is a feature of the OptionButton and ToggleButton controls as well.
TripleState
is a Boolean property that determines whether the check box, option button, or toggle button can have a null state as well as True
and False
states. When a check box or other control is in the null state, it appears with a small black square in its box.
You can see the null state in the Font dialog box in Word when one of the check-box–controlled properties—such as the Strikethrough check box in Figure 14.10—is true for some but not all of the current selection. For example, select some text where part of it is struck through but other characters are not struck. This will trigger the null state for the Strikethrough check box, as shown in Figure 14.10. Here Word's Font dialog box shows the Strikethrough check box in a null state (containing a small black square, but not checked). Normally, a check box is either checked or not, but when in a null state, it contains a small black square, indicating it's neither true nor false. (In earlier versions of Office, the null state in a check box was indicated by filling the box with gray or black.)
A couple of properties described briefly in the context of other controls deserve more detail here:
SpecialEffect
property controls the visual appearance of the check box. The default value is fmButtonEffectSunken (2)
, which displays a sunken box—the norm for 3D Windows dialog boxes.fmButtonEffectFlat(0)
to display a box with a flat effect, but why? To me, it doesn't look as good as the default 3D, shadowed box.
The flat version is less subtle, crude actually. But, it fits in well with the “Modern” aesthetic introduced by Microsoft back with Windows 8—no gradients, opacity, dimensional effects, shadows, subtle colors, highlights, reflections, serif typefaces, and so on. In other words, flatland.
Figure 14.11 shows a sunken check box and a flat check box. The Value
property, which indicates whether the check box is selected (True
) or cleared (False
), is the default property of the check box. Recall that the default property need not be specified in code. It's assumed. Thus, you can either write CheckBox1.Value
or just CheckBox
. The following three statements have the same effect:
If CheckBox1.Value = True Then
If CheckBox1 = True Then
If CheckBox1 Then
Accelerator
property provides quick access to the check box. Assign a unique accelerator key to check boxes so that the user can swiftly toggle them on and off from the keyboard.OptionButtons are useful when you want the user to choose one item in a group. A group of OptionButtons displays a set of mutually exclusive options. Only one of the buttons in a group can be selected at a given time. For instance, you could have two OptionButtons under the heading Sex: Male and Female. (Recall that a set of CheckBoxes permits multiple options to be chosen simultaneously. CheckBoxes are useful for choosing more complex options. For example, under the heading Typeface, you could have Italic, Bold, and Underlined options, all of which could be selected simultaneously.)
Like the CheckBox, the OptionButton control has a straightforward set of properties, almost all of which you've seen already in this chapter. This section shows you the GroupName
property, which is unique to the OptionButton, and some of the key properties for working with option buttons.
The GroupName
property is a String data type that assigns the option button to a group of option buttons. Alternatively, you can create a group by placing a set of option buttons on a Frame control. The key idea here is that, once grouped, the buttons become mutually exclusive. However, there can be more than one group (or set) on a form—as long as you employ a Frame control or the GroupName
property to isolate the various groups of buttons.
The default setting for GroupName
is a blank string (""
), which means that an option button isn't assigned to a group until you explicitly assign it. When you enter the group name, the group is created. By using the GroupName
property, you can have multiple groups of option buttons on the same form without using frames to specify groups, but you must somehow distinguish the logical groups of option buttons from each other so that the user can tell which option buttons constitute a group. Using a Frame control is the easiest way of segregating groups of option buttons both visually and logically—but it's useful to have the flexibility that GroupName
provides when you need it. Also, a Frame has a built-in Caption
property you can use to describe the group's purpose.
These are the other key properties of the OptionButton control:
Value
property, which indicates whether the option button is selected (True
) or cleared (False
), is the default property of the option button. So, you can set or return the state of the option button by setting either the OptionButton
object or its Value
to True
or False
, as appropriate. Setting the Value
of one OptionButton to True
sets the Value
of all other OptionButton controls in the same group or frame to False
.Accelerator
property provides quick access to the option button. Assign a unique accelerator key to each option button so that the user can toggle it on and off from the keyboard.SpecialEffect
property controls the visual appearance of the option button. The default value of fmButtonEffectSunken
(2
) displays a sunken button, while fmButton EffectFlat
(0
) displays a flattened button. Figure 14.11 shows a sunken option button and a flat option button.TripleState
property (discussed in the previous section, “CheckBox”) lets you create an option button that has three states: selected (True
), cleared (False
), and null (which appears selected but grayed out). The TripleState
property is disabled so that the user can't set the null state interactively, but you can set it programmatically as needed.This button is like a light switch; essentially, it's either on or off. When it's not selected, the ToggleButton control appears raised, but it looks pushed in when it's selected. The key properties for the ToggleButton control are the same as those for the CheckBox and CommandButton:
Value
property is the default property of the ToggleButton.TripleState
property is rarely used, but lets you create a ToggleButton that has three states: selected (True
), cleared (False
), and null. The user can set a triple-state ToggleButton to its null state by clicking it. In its null state, a ToggleButton appears selected, but gray.Accelerator
property provides quick access to the toggle button.The Frame control is relatively straightforward, but it has several properties worth mentioning; they're shown in Table 14.5. The Frame control shares a couple of these properties with the Page
object.
TABLE 14.5: Properties of the Frame control
PROPERTY | DESCRIPTION |
Cycle |
Determines the action taken when the user leaves the last control in the frame or on the page. fmCycleAllForms (0 ) moves the focus to the next control in the tab order for the user form or page, whereas fmCycle CurrentForm (2 ) keeps the focus within the frame or on the page until the focus is explicitly moved to a control in a different frame or on a different page. This property applies to the Page object as well. |
InsideHeight |
A read-only property that returns the height (measured in points) of the area inside the frame, not including the height of any horizontal scroll bar displayed. This property applies to the Page object as well. |
InsideWidth |
A read-only property that returns the width (in points) of the area inside the frame, not including the width of any vertical scroll bar displayed. This property applies to the Page object as well. |
KeepScrollBarsVisible |
A property that determines whether the frame or page displays horizontal and vertical scroll bars when they aren't required for the user to be able to navigate the frame or the page. fmScrollBarsNone (0 ) displays no scroll bars unless they're required. fmScrollBarsHorizontal (1 ) displays a horizontal scroll bar all the time. fmScrollBarsVertical (2 ) displays a vertical scroll bar all the time. fmScrollBarsBoth (3 ) displays a horizontal scroll bar and a vertical scroll bar all the time. fmScrollBarsNone is the default for the Frame object, and fmScrollBarsBoth is the default for the Page object. This property applies to the Page object as well. |
PictureTiling |
A Boolean property that determines whether a picture displayed on the control is tiled (True ) so that it takes up the whole area covered by the control or not (False ). To set the tiling pattern, you use the PictureAlignment and PictureSizeMode properties. This property applies to the Page object and the Image control as well. |
PictureSizeMode |
Determines how to display the background picture. fmPictureSizeModeClip (0 ), the default setting, crops (removes) any part of the picture too big to fit in the page, frame, or image control. Use this setting to show the picture at its original dimensions and in its original proportions. fmPictureSizeModeStretch (1 ) stretches the picture horizontally or vertically to fill the page, frame, or image control. This setting is good for colored backgrounds and decorative effects but tends to be disastrous for pictures that need to be recognizable. It also overrides the PictureAlignment property setting. fmPictureSizeModeZoom (3 ) zooms the picture proportionately until the horizontal dimension or the vertical dimension reaches the edge of the control but doesn't stretch the picture so that the other dimension is maximized as well. This is good for maximizing the size of a picture while retaining its proportions, but you'll need to resize the nonmaximized dimension to remove blank spaces. This property applies to the Page object and the Image control as well. |
PictureAlignment |
Determines where a picture is located. fmPictureAlignmentTopLeft (0 ) aligns the picture with the upper-left corner of the control. fmPictureAlignmentTopRight (1 ) aligns the picture with the upper-right corner of the control. fmPictureAlignmentCenter (2 ), the default setting, centers the picture in the control (both horizontally and vertically). fmPictureAlignmentBottomLeft (3 ) aligns the picture with the lower-left corner of the control. fmPictureAlignmentBottomRight (4 ) aligns the picture with the lower-right corner of the control. This property applies to the Page object and the Image control as well. |
The CommandButton is used quite often. This control has three unique properties, listed in Table 14.6.
TABLE 14.6: Unique properties of the CommandButton control
PROPERTY | DESCRIPTION |
Cancel |
A Boolean property that determines whether the command button is the Cancel button for the user form (True ) or not (False ). The Cancel button for a user form can bear any name. What distinguishes it is that its Cancel property is set to True . The Cancel button is activated by the user's pressing Esc, or clicking the button, or putting the focus on the button and pressing Enter. Only one command button on a form can be the Cancel button at any given time. Setting the Cancel property for a command button to True causes VBA to set the Cancel property to False for any button for which it was previously set to True . |
Default |
A Boolean property that determines whether the command button is the default button for the user form (True ) or not (False ). Only one command button on a form can be the default button at any given time. Setting the Default property for a command button to True causes VBA to set the Default property to False for any button for which it was previously set to True . The default button is activated by the user pressing Enter when the focus isn't on any other command button. |
TakeFocusOnClick |
A Boolean property that determines whether the command button takes the focus when the user clicks it (True ) or not (False ). The default setting for this property is True , but you may want to set it to False when you need the focus to remain on another control in the user form even when the user clicks the command button. However, if the user uses the Tab key or the arrow keys to move to the command button, the command button will take the focus even if the TakeFocusOnClick property is set to False . |
Note that it's useful to set the Accelerator
property for each command button on a form. This way, the user can quickly access it from the keyboard.
TabStrip controls allow you to create a multipage dialog box. Click the Home tab in Word and then click the small arrow icon in the lower-right corner of the Font area on the Ribbon. Word's Font dialog box will open and you'll see a two-tab dialog box. One tab is labeled Font and the other tab is labeled Advanced. This is a good way to organize a dialog box when you have quite a few options to present to the user.
The TabStrip control has several unique properties and a number of properties that it shares with the MultiPage control. Table 14.7 lists these properties.
TABLE 14.7: Properties of the TabStrip and MultiPage controls
PROPERTY | DESCRIPTION |
ClientHeight |
(Tab strip only.) A Single (data type) property that sets or returns the height of the display area of the tab strip, measured in points. |
ClientLeft |
(Tab strip only.) A Single property that returns the distance, measured in points, between the left border of the tab strip and the left border of the control inside it. |
ClientTop |
(Tab strip only.) A Single property that returns the distance, measured in points, between the top border of the tab strip and the top border of the control inside it. |
ClientWidth |
(Tab strip only.) A Single property that sets or returns the width of the display area of the tab strip, measured in points. |
SelectedItem |
Sets or returns the tab currently selected in a tab strip or the page currently selected in a MultiPage control. |
TabFixedHeight |
A Single property that sets or returns the fixed height of the tabs, measured in points. Set TabFixedHeight to 0 to have the tabs automatically size themselves to fit their contents. |
TabFixedWidth |
A Single property that sets or returns the fixed width of the tabs, measured in points. Set TabFixedWidth to 0 to have the tabs automatically size themselves to fit their contents. |
TabOrientation |
Determines the location of the tabs in the tab strip or multipage. fmTabOrientationTop (0 ), the default, displays the tabs at the top of the tab strip or multipage. fmTabOrientationBottom (1 ) displays the tabs at the bottom of the tab strip or multipage. fmTabOrientationLeft (2 ) displays the tabs at the left of the tab strip or multipage, and fmTabOrientationRight displays the tabs at the right of the tab strip or multipage. |
A SpinButton allows the user to easily increment or decrement numbers, dates, and so on. The ScrollBar and SpinButton share a number of properties that you haven't yet encountered. Table 14.8 lists these properties.
TABLE 14.8: Properties of the ScrollBar and SpinButton controls
PROPERTY | DESCRIPTION |
Delay |
A Long (data type) property that sets the delay in milliseconds between clicks registered on the control when the user clicks and holds down the mouse button. The default delay is 50 milliseconds. The control registers the first click immediately, the second click after Delay x 5 (the extra delay is to assist the user in clicking only once), and the third and subsequent clicks after Delay . |
LargeChange |
(Scroll bar only.) A Long property that determines how much the item is scrolled when the user clicks in the scroll bar between the thumb (the small square within the scroll bar) and the scroll bar's arrow. Set the LargeChange property after setting the Max and Min properties of the scroll bar. |
SmallChange |
A Long property that determines how much movement occurs when the user clicks a scroll arrow in a scroll bar or spin button. SmallChange needs to be an integer value; the default value is 1 . |
Max |
A Long property that specifies the maximum value for the Value property of the scroll bar or spin button. Max must be an integer. The default value is 1 . |
Min |
A Long property that specifies the minimum value for the Value property of the scroll bar or spin button. Min must be an integer. The default value is 1 . |
ProportionalThumb |
(Scroll bar only.) A Boolean property that determines whether the thumb is a fixed size (False ) or is proportional to the size of the scrolling region (True ), thereby giving the user an approximate idea of how much of the scrolling region is currently visible. The default setting is True . |
By now, you've seen all the properties of the Image control. Most of the time when you use an Image control, you'll want to adjust the following properties:
Picture
property to assign the picture file you want to appear in the Image control:
The Image control can display .BMP
, .CUR
(cursor), .GIF
, .ICO
(icon), .JPG
, and .WMF
files, but not other graphics files, such as .TIF
(aka .TIFF
) Most graphics applications, however, can easily convert one graphics file type into another.
PictureAlignment
property to set the alignment of the picture.PictureSizeMode
property to set whether the picture is clipped, stretched, or zoomed to fill the Image control. Adjust the height and width of the Image control as necessary.PictureTiling
property if you need to tile the image to take up the full space in the control.The Page
object is one of the pages contained within a MultiPage
object. You've already seen all its properties (in the context of other controls) except for the Index
property, which it shares with the Tab
object.
The Index
property is an Integer data type that determines the position of the Page
object in the Pages
collection in a MultiPage control or the position of a Tab
object in the Tabs
collection in a TabStrip. The first Page
object or Tab
object is numbered 0 (zero), the second Page
or Tab
object is numbered 1, and so on. You can change the Index
property of a tab or page to change the position in which the tab or page appears in the collection.
The Tab
object is one of the tabs contained within a TabStrip
object. You've already learned about all its properties in the context of other controls.
As mentioned briefly earlier in this chapter, when you're designing a form, it's often handy to group controls. By grouping two or more controls, you can work with them as a single unit to size, reposition, format, or delete them. (Recall that this form-design grouping technique has nothing to do with creating a set of option buttons within a Frame control. That creates a mutually exclusive collection of radio buttons to display to the user during runtime.)
To group controls, select them by Shift+clicking, Ctrl+clicking, or dragging around them, and then right-click and choose Group from the context menu. Alternatively, select the controls, and then click the Group button on the UserForm toolbar (you'll need to display this toolbar—it's not displayed by default) or choose Format ➢ Group. VBA creates a new group containing the controls and places a shaded border with handles around the whole group, as shown on the right in Figure 14.12.
When you merely select a set of controls (by Shift+clicking, Ctrl+clicking, or dragging around them), you have only temporarily grouped them. You can still manipulate them as a group, but as soon as you deselect them—by, for example, clicking the background of the form itself—the grouping disappears. However, when you right-click and choose Group from the context menu, they will remain grouped until you right-click and choose Ungroup.
To ungroup controls, right-click any of the controls contained in the group and then choose Ungroup from the context menu. Alternatively, select the group of controls by clicking in any control in the group and then click the Ungroup button on the UserForm toolbar, or choose Format ➢ Ungroup. VBA removes the shaded border with handles from around the group and displays the normal border and handles around each individual control.
You can quickly size all controls in a group by selecting the group and then dragging the sizing handles on the surrounding border. For example, you could select the middle handle on the right side and drag it inward to shorten the controls, as shown in Figure 14.13. The controls will be resized proportionately to the change in the group outline.
When the controls are grouped, you can then use the Properties window to quickly modify any properties they have in common (such as Font). But resizing a group can present problems—the results can be ugly. Generally speaking, resizing works fine when you've grouped a number of controls of the same type, as in Figure 14.13. For example, sizing a group that consists of several command buttons or option buttons works well, whereas sizing a group that consists of a text box, a command button, and a combo box is seldom a good idea.
You can quickly delete a whole group of controls by right-clicking any of them and choosing Delete from the context menu or by selecting the group and pressing the Delete key.
Even after you've grouped a number of controls, you can still work with them individually if necessary. To do this, first click any control in the group to select the group as a whole, as shown on the left in Figure 14.14. Then click the control you want to work with. As shown on the right in Figure 14.14, VBA displays a dark shaded border around the group (indicating that the group still exists) and displays the lighter shaded border around the individual control, indicating that that control is selected.
You can then modify the selected individual control as if it were not grouped. Change its ForeColor property to blue, for instance, and only the caption in that particular control will turn blue. When you've finished working with it, click another control in the group to individually select it, or click the background of user form to deselect all individual controls and restore the group.
Even if you use the Snap To Grid feature, you'll often need to align controls manually. They must be ungrouped for this feature to work. The easiest way to align controls is to select two or more, then right-click in any one of them, and choose an option from the Align submenu: Lefts, Centers, Rights, Tops, Middles, Bottoms, or To Grid. These options work as follows:
VBA aligns the borders or midpoints to the current position of that border or midpoint on the dominant control—the control that has white sizing handles around it rather than black sizing handles. After selecting the controls you want to align, make dominant the one that is already in the correct position by clicking it so that it takes on the white sizing handles. Then choose the alignment option you want.
The VBA Editor offers several placement commands on the Format menu:
The tab order of a user form (or of a Frame control within a form) is the order in which VBA selects controls in the form or frame when the user moves through them by pressing the Tab key (to move forward) or the Shift+Tab key combination (to move backward).
Put another way, it's a Windows convention that when the user presses the Tab key, the focus moves to the next control in a window.
Only one control at a time can have the focus. For example, if a form has five text boxes, only one of these text boxes, the one that currently has the focus, will display characters as the user types. In addition, a button in a set of buttons can also have the focus, and when the user presses the Enter key, the button with the focus will be triggered. Or the user can click a different button to move the focus to that button.
VBA displays a visual cue to indicate which control currently has the focus. You'll see a dotted frame around a button or option button and a blinking insertion cursor in a text box.
Each frame you add to a user form has a separate tab order for the controls it contains: The frame itself appears in the tab order for the form, and the controls within the frame appear in the tab order for the frame.
Set the tab order for the controls on a form or in a frame to make it as easy as possible for the user to work with your form. Generally, for English-speaking users, it's best to arrange the tab order from left to right and from top to bottom in the dialog box or frame. For international users, you may want to arrange the tab order from right to left. You may also need to arrange the tab order to move from one control to a related control that would not normally be next in the tab order.
The whole point of managing the tab order is that you simplify things for your user. Employing the Tab key in this way allows users to fill in a whole form without once having to move their hand off the keyboard to keep selecting, with a mouse click, each next text box.
This kind of tabbing is particularly useful when the user is asked to fill in several fields by typing into multiple text boxes (such as Name, Address, Phone, and so on). As soon as users finish filling in one field, they can press Tab to move on to the next. (Even easier, pressing the Enter key while in a text box moves users to the next control in the tab order.) At the end, after they've filled in the last field, they can quickly close the dialog box if you make the OK button the next control in the tab order.
VBA assigns the tab order to the controls in a dialog box or frame on a first-come, first-served basis as you add the controls. Unless you add all the controls in perfect order, this default order will seldom produce the optimal tab order for a dialog box, so usually you'll want to adjust the tab order—or at least check to ensure that it's right. You're likely to place fewer controls on a frame than on a form, so you have a better chance of adding them in a suitable order, but you should check these controls too before unleashing the dialog box on users.
Just press F5 and then repeatedly press the Tab key to examine your current tab order. Alternatively, you can open a Tab Order dialog box (shown in Figure 14.15) by right-clicking in the open space in the background of the form or frame and choosing Tab Order from the context menu. Or you can select the user form or frame and then choose View ➢ Tab Order.
It's best to adjust the tab order after you've finished creating your form (adding a control later will require that you go back and modify the tab order). Here's how to change the tab order in a dialog box or frame:
You can Shift+click or drag to select a range of controls, or Ctrl+click to select two or more noncontiguous controls. (Or you can just change the controls’ TabIndex properties in the Properties window.)
Designing a custom form is only the first step in getting it to work in a procedure. The other step is writing the code to display the form to the user and make it perform its tasks.
Typically, the code for a form consists of the following:
Show
method.
Usually, this procedure can be assigned to a Quick Access Toolbar button or to a shortcut key combination so that the user can conveniently invoke it. However, a procedure can also be designed to run automatically in response to a system event (such as running at a specified time or when a worksheet is opened).
For example, for a simple dialog box containing two option buttons and two command buttons (an OK button and a Cancel button), you'd typically write one procedure for the OK button and one for the Cancel button. The procedure for the OK button is executed when the user either clicks the button with the mouse or presses the Enter key while the focus is on that button. Either of these user actions triggers the button's Click
event, and whatever code you, the programmer, have written within this event is then executed.
Remember that the easiest way to create an event (procedure) for a control is to just double-click the control on the form. The Editor then switches to Code view and writes the necessary Sub…End Sub
envelope for that event, like this:
Private Sub btnOK_Click()
End Sub
Notice that the Editor automatically combines the Name
property of the control with the name of the event as the procedure's name, separated by an underscore character: btnOK_Click
.
Once the code attached to a button has run, execution returns to the form (if it's still displayed) or to the procedure that called the form.
Note that code that runs directly in response to an event is called an event procedure or event handler. An event procedure can call other procedures as necessary, so multiple procedures can be run indirectly when a single event handler Sub
is triggered.
You load a form by using the Load
statement, and unload it by using the Unload
statement. The Load
statement loads the form into memory so that it's available to the program but doesn't display the form. For that you use the Show
method (discussed in the next section). The Unload
statement unloads the form from memory and releases any memory associated with that object. If the form is displayed when the Unload
statement runs, VBA removes the form from the screen.
The syntax for the Load
and Unload
statements is straightforward:
Load UserForm1
Unload UserForm1
Here, UserForm1
is the name of the user form or dialog box. For example, the following statement loads the dialog box named frmMyDialog
:
Load frmMyDialog
To display a form, you use the Show
method; to hide a form, you use the Hide
method. For example, the following statement displays the form named frmMyDialog
:
frmMyDialog.Show
If you execute a procedure containing this line, the frmMyDialog
form appears onscreen so the user can interact with it: enter text in its text boxes, select or clear its check boxes, use its drop-down lists, click its buttons, and so on.
When the user closes the form (by clicking the Close button on its title bar or by clicking a command button that dismisses it), the form disappears from the screen and the procedure continues to run. But until you retrieve settings from the form and take action on them, the form has no effect beyond its graphical display.
You can display a form by using the Show
method without explicitly loading the form with a Load
command first. VBA takes care of the implied Load
command for you. There's no particular advantage to including the Load
command, but it might make your code easier to read and to debug. For example, the two procedures shown here have the same effect:
Sub Display_Dialog()
Load frmMyDialog 'loads the form into memory
frmMyDialog.Show 'displays the form
End Sub
Sub Display_Dialog()
frmMyDialog.Show 'loads the form into memory and displays it
End Sub
If you run a Hide
method without having loaded the form into memory by using the Load
statement or the Show
method, VBA loads the form but does not display it onscreen.
Once you've displayed the form, take a moment to check its tab order by pressing F5 and then moving through it using the Tab key. When you first open the form, is the focus on the appropriate control, the control the user is most likely to want to interact with first? When you move forward from that control, is the next control that is selected the next control that the user will typically need to use? Adjust the tab order as necessary, as described in “Adjusting the Tab Order of a Form” earlier in this chapter.
To specify a default command button in a form, set that command button's Default
property to True
. VBA selects the default button when it displays the form so that if the user simply presses the Enter key to dismiss the dialog box, this button receives the keystroke.
Only one button at a time can be the default button. If you set the Default
property of any button to True
, VBA automatically changes to False
the Default
property of any other button previously set to True
.
To make a form do something, your code will usually respond to the user's input. The following sections first cover the VBA commands for retrieving information from a dialog box. Then you'll see an example of how to retrieve the user's choices from both a relatively simple dialog box and then a more complex form.
To return (retrieve, for use in your code) a string from a text box, your code can check its Value
property or Text
property after the user has clicked an OK or Cancel button or otherwise dismissed the dialog box.
For example, if you have a text box named txtMyText
, you could return its value and display it in a message box by using the following line:
MsgBox txtMyText.Value
Recall that VBA supports both one-line and multiline text boxes. To create a multiline text box, select the text box in the user form or in the drop-down list in the Properties window and set its MultiLine
property to True
. The user can then enter multiple lines in the text box and start new lines by pressing Shift+Enter.
To add a horizontal or vertical scroll bar to a text box, set its ScrollBars
property to 1 - fmScrollBarsHorizontal
(for a horizontal scroll bar), 2 - fmScrollBarsVertical
(for a vertical scroll bar, which is usually more useful), or 3 - fmScrollBarsBoth
(for both).
A regular option button is a binary control, so it can have only two values: True
and False
. True
indicates that the button is selected, False
that it's unselected. You can check an option button's value with a simple If…Then
structure. For example, if you have two option buttons, named optSearchForFile
and optUseThisFile
, you can check their values and find out which was selected by using the following code:
If optSearchForFile = True Then
'optSearchForFile was selected; take action on this
Else 'optSearchForFile was not selected, so optUseThisFile was
'take action for optUseThisFile
End If
Remember that Value
is the default property of the OptionButton control. The previous code checks the value of the default property of the control, so you need not specify the property in your code. Default properties can be omitted as a kind of shorthand programming. The first line of code could be written out more fully as If optSearchForFile.Value = True Then
. But in the code example, I chose to write it more succinctly, with = True
implied: If optSearchForFile Then
.
With more than two option buttons, use an If…Then…ElseIf
condition or a Select Case
statement to determine which option button is selected.
Like an option button, a regular check box can only be either True
or False
, so you can use an If…Then
structure to check its value. Here's an example:
If chkDisplayProgress = True Then
'take actions for chkDisplayProgress
End If
Again, you're checking the default property of the control here—the Value
property. The first line of code could also be written as If chkDisplayProgress.Value = True Then
.
Sometimes you'll need to take an action if the check box was cleared (deselected) rather than selected. For example, if the user clears the check box, you may need to turn off a configuration option.
List boxes start out empty. So, before you can ask the user to choose an item in a list box, you must first fill the box with items from which the user can choose—you must tell VBA which items to display. To do so, you create a procedure to initialize (prepare) the user form and add the items to the list box before displaying it:
Or you can just double-click somewhere in the background on the user form to go to Code view. Recall that you can toggle between the Code window (press F7) and the form-design window (Shift+F7).
The Visual Basic Editor creates a new procedure named Private Sub UserForm_Initialize
for you, inserting it at the end of the procedures currently displayed in the Code window:
Private Sub UserForm_Initialize()
End Sub
Here's a tip: VBA runs a UserForm_Initialize
procedure every time the user form is brought to life. This procedure is a good place to add items to a list box or combo box or to set properties of other controls on the user form. In other words, this Initialize
event is where you write code to do any necessary preliminary housekeeping before displaying the form to the user.
AddItem
method for the list box object (here the box is named lstBatteries
) with a text string in quotation marks to display the ID number of each battery in the list box:
lstBatteries.AddItem "Battery #A4601"
lstBatteries.AddItem "Battery #A4602"
lstBatteries.AddItem "Battery #A4603"
lstBatteries.AddItem "Battery #A4604"
To retrieve the user's choice from a single-select-style list box, check the Value
property in your code, as in this example:
MsgBox "You chose this entry from the list box: " & lstBattery.Value
Single-select list boxes are like a set of option buttons—the user is allowed to select only one of them.
When you use the MultiSelect
property to create a list box capable of multiple selections, you can no longer use the Value
property to return the items selected in the list box. When MultiSelect
is set to True
, Value
always returns a null value. Instead, you use the Selected
property to determine which rows in the list box are selected and the List
property (it's an array) to return the contents (the values) of each selected row.
The following statements use a For…Next
loop to build a string named strMsg
containing the entries selected from a multiselect list box:
strMsg = "You chose the following entries from the list box: " & vbCr
For i = 1 To lstBatteries.ListCount
If lstBatteries.Selected(i - 1) = True Then
strMsg = strMsg & lstBatteries.List(i - 1) & vbCr
End If
Next i
MsgBox strMsg
To return a value from a combo box (a control that is, in effect, a combination list box and text box), you add items to the combo box list in an Initialize
procedure and then check the Value
of the combo box after the user has dismissed the dialog box. (The combo box control doesn't offer multiple-selection capabilities, so Value
is the property to check.)
For example, you would use the following code to add items to a combo box named cmbColor
:
Private Sub UserForm_Initialize()
cmbColor.AddItem "Red"
cmbColor.AddItem "Blue"
cmbColor.AddItem "Yellow"
End Sub
To return the item the user chose in the combo box, retrieve the Value
property:
Result = cmbColor.Value
The item retrieved from a combo box can be either one of the items assigned in the Initialize
procedure or one that the user has typed into the text-box portion of the combo box.
The following sections show you two examples of how you can create a procedure and then design a form that works with it to make the procedure more useful and powerful. In the first example, you'll record a macro in Word and then link a form to that code. In the second example, which will work with any VBA-enabled application, you'll create a user form and its associated code from scratch.
This first example moves the current paragraph up or down within the document by one or two paragraphs in Word.
Start by recording a procedure in Word to move the current paragraph. In the procedure, you need to record the commands for the following actions:
We want our finished procedure to display a dialog box with option buttons for moving the current paragraph up one paragraph, up two paragraphs, down one paragraph, or down two paragraphs. The dialog box should also include a check box that indicates the user wants the insertion point returned to its original position at the end of the procedure. Because this is presumably desirable default behavior for the procedure, this check box is selected by default. Users can clear the check box if they don't want to return the insertion point to its original position.
First, start Word and create a new, blank, scratch document (press Ctrl+N), and enter three or four paragraphs of text—just about anything will do, but it'll be easier to have actual text, not random nonsense. That way you can make sure the procedure is moving paragraphs as it should. Then place the insertion point in one of the paragraphs you've just entered and start recording a macro as discussed in Chapter 1, “Recording and Running Macros in the Office Applications”:
Move_Paragraph
, in the Macro Name text box and a description in the Description text box.You probably don't want to add this to the global NewMacros
module in the Normal.dotm
file. Why clutter it up with practice macros?
Record the following actions in the macro:
In this example, the bookmark is named Move_Paragraph_Temp
to indicate that it's a temporary bookmark used for the Move_Paragraph
procedure.
The first press of F8 activates Extend mode, the second selects the current word, the third selects the current sentence, and the fourth selects the current paragraph.
Note that if you started with the insertion point at the beginning of the first paragraph in the document, you'll only be able to move the insertion point up one paragraph. This doesn't matter—press the keystroke anyway to record it. If Word beeps at you, ignore it.
If in doing so you hit the end of the document after the first keystroke, don't worry—perform the second keystroke anyway to record it. Word may sound a beep.
Move_Paragraph_Temp
bookmark, and click the Go To button to go to it.Move_Paragraph_Temp
bookmark.Open the recorded macro in the Visual Basic Editor by clicking Developer ➢ Macros, selecting the macro's name in the Macros dialog box, and clicking the Edit button.
You should see a macro that looks something like this:
1. Sub Move_Paragraph()
2. '
3. ' Move_Paragraph Macro
4. ' Move a paragraph up or down
5. '
6. With ActiveDocument.Bookmarks
7. .Add Range:=Selection.Range, Name:="Move_Paragraph_Temp"
8. .DefaultSorting = wdSortByName
9. .ShowHidden = False
10. End With
11. Selection.Extend
12. Selection.Extend
13. Selection.Extend
14. Selection.Extend
15. Selection.EscapeKey
16. Selection.Cut
17. Selection.MoveUp Unit:=wdParagraph, Count:=1
18. Selection.Paste
19. Selection.MoveDown Unit:=wdParagraph, Count:=1
20. Selection.MoveUp Unit:=wdParagraph, Count:=2
21. Selection.MoveDown Unit:=wdParagraph, Count:=2
22. Selection.GoTo What:=wdGoToBookmark, Name:="Move_Paragraph_Temp"
23. ActiveDocument.Bookmarks("Move_Paragraph_Temp").Delete
24. With ActiveDocument.Bookmarks
25. .DefaultSorting = wdSortByName
26. .ShowHidden = False
27. End With
28. End Sub
You can probably read this macro code easily enough by now:
With
statement that adds the Move_Paragraph_Temp
bookmark.Move_Paragraph_Temp
bookmark, and line 23 deletes the bookmark.If you like, you can quickly delete unnecessary lines of code, and collapse the first With
structure, to create a more succinct, more easily understood, version of the code:
1. Sub Move_Paragraph()
2. ActiveDocument.Bookmarks.Add Range:=Selection.Range, _
Name:="Move_Paragraph_Temp"
3. Selection.Extend
4. Selection.Extend
5. Selection.Extend
6. Selection.Extend
7. Selection.EscapeKey
8. Selection.Cut
9. Selection.MoveUp Unit:=wdParagraph, Count:=1
10. Selection.Paste
11. Selection.MoveDown Unit:=wdParagraph, Count:=1
12. Selection.MoveUp Unit:=wdParagraph, Count:=2
13. Selection.MoveDown Unit:=wdParagraph, Count:=2
14. Selection.GoTo What:=wdGoToBookmark, _
Name:="Move_Paragraph_Temp"
15. End Sub
Next, create the dialog box for the procedure (see Figure 14.16):
Name
and Caption
properties:
Name
cell and enter the Name
property there.Caption
cell and enter the Caption
property. The example user form is named frmMoveParagraph
and has the caption Move Current Paragraph
so that the name of the form is closely related to the text the user will see in the title bar of the dialog box but different from the procedure name (Move_Current_Paragraph
).Movement
and the bottom frame Insertion Point
by selecting each in turn and then setting the Caption
property in the Properties window. Then name the top frame fraMovement
and the bottom frame fraInsertionPoint
.You can do all these things freehand if you prefer by just eyeballing. Drag them around until you have them neatly positioned and sized.
Caption
property in the Properties window.
Caption them as illustrated in Figure 14.18: Up one paragraph
, Up two paragraphs
, Down one paragraph
, and Down two paragraphs
. These option buttons will control the number of paragraphs the procedure moves the current paragraph.
For example, to reveal hidden text that's cut off on the right side, drag the handle at the right midpoint of the group outward.
optUpOne
, optUpTwo
, optDownOne
, and optDownTwo
, respectively, by changing the Name
property of each in turn in the Properties window.Value
property to True
by selecting the default False
value in the Properties window and entering True
instead.
Doing so will select the option button in the user form you're designing, and when the dialog box is displayed, that option button will be selected as the default choice for the option group.
U
as its Accelerator
property. Set the Accelerator
property of the second option button to t, the third to D, and the fourth to w.
The Accelerator
property is case-sensitive only when the caption for the control contains both the uppercase and lowercase versions of the same letter.
chkReturnToPrevious Position
(a long name but a descriptive one).Caption
property to Return to previous position
.R
as its Accelerator
property.True
as its Value
property.Name
property to cmdOK
.Caption
property to OK
.Accelerator
property to O
(that's O as in OK, not a zero).Default
property to True
.Name
property to cmdCancel
.Accelerator
property to A
.Caption
property to Cancel
.Cancel
property to True
.Default
property set to False
.Private Sub cmdCancel_Click()
End Sub
Recall that the Editor chooses to create a procedure for the most common event for whatever control (or the form) you double-click to get down into the Code window. For most controls, this will be the Click
event, as it is for the CommandButton control.
Type an End
statement between the lines:
Private Sub cmdCancel_Click()
End
End Sub
This End
statement removes the form from the screen and ends the current procedure—in this case, the Move_Current_Paragraph
procedure.
Now you'll attach code to the OK button, which is where things get interesting. When the user clicks the OK button, the procedure needs to continue executing and do all of the following:
Value
property of the check box to see whether it was selected or cleared.Value
property of each option button in turn to see which of them was selected when the OK button was clicked.Now continue creating the Move Current Paragraph dialog box:
If you're still working in the Code window, select cmdOK in the Object drop-down list (on the top left of the Code window). The Editor automatically creates the Click
event procedure for this button.
Private Sub
and End Sub
lines:
frmMoveParagraph.Hide
Unload frmMoveParagraph
The frmMoveParagraph.Hide
line activates the Hide
method for the frmMoveParagraph
user form, hiding it from display on the screen. The Unload frmMoveParagraph
line unloads the dialog box from memory.
Value
property of the chkReturnToPreviousPosition
check box to find out whether to insert a bookmark in the document to mark the current position of the insertion point. To do this, enter a straightforward If…Then
statement:
If chkReturnToPreviousPosition = True Then
End If
If the chkReturnToPreviousPosition
statement is set to True
—that is, if the check box is selected—the code in the lines following the Then
statement runs. The Then
statement consists of the lines for inserting a bookmark that you recorded earlier.
If…Then
statement like this:
If chkReturnToPreviousPosition = True Then
With ActiveDocument.Bookmarks
.Add Range:=Selection.Range, Name:=" Move_Paragraph_Temp"
End With
End If
If the check box is selected, the procedure inserts a bookmark. If the check box is cleared, the procedure passes over these lines.
End If
, paste in the code for selecting the current paragraph and cutting it to the Clipboard:
Selection.Extend
Selection.Extend
Selection.Extend
Selection.Extend
Selection.Cut
Value
properties from the option buttons to see which one was selected when the user chose the OK button in the dialog box. For this, you can again use an If
condition—this time, an If…Then ElseIf…Else
condition, with the relevant insertion-point-movement lines from the recorded procedure pasted in:
If optUpOne = True Then
Selection.MoveUp Unit:=wdParagraph, Count:=1
ElseIf optUpTwo = True Then
Selection.MoveUp Unit:=wdParagraph, Count:=2
ElseIf optDownOne = True Then
Selection.MoveDown Unit:=wdParagraph, Count:=1
Else
Selection.MoveDown Unit:=wdParagraph, Count:=2
End If
Selection.Paste
Here, optUpOne
, optUpTwo
, optDownOne
, and optDownTwo
(which uses the Else
statement here and, therefore, isn't specified by name in the listing) are the four option buttons from the dialog box, representing the choice to move the current paragraph up one paragraph, up two paragraphs, down one paragraph, or down two paragraphs, respectively.
The condition is straightforward: If optUpOne
is True
(that is, if this option button is selected), the first Then
condition executes, moving the insertion point up one paragraph from its current position (after the current paragraph is cut, the insertion point will be at the beginning of the paragraph that was after the current one). If optUpOne
is False
, the first ElseIf
condition is evaluated. If the condition evaluates to True
, the second Then
condition executes. And if the condition evaluates to False
, the next ElseIf
condition is evaluated. If that condition, too, turns out to be False
, the Else
code is run. In this case, the Else
statement means that the optDownTwo
option button was selected in the dialog box, so the Else
code moves the insertion point down two paragraphs.
Wherever the insertion point ends based on which option button the user chose, the next line of code (Selection.Paste
) pastes in the cut paragraph from the Clipboard.
chkReturnToPreviousPosition
check box is selected. Again, you can test for this with a simple If…Then
condition that incorporates the go-to-bookmark and delete-bookmark lines from the recorded procedure:
If chkReturnToPreviousPosition = True Then
Selection.GoTo What:=wdGoToBookmark, _
Name:=" Move_Paragraph_Temp"
ActiveDocument.Bookmarks("Move_Paragraph_Temp").Delete
End If
If the chkReturnToPreviousPosition
check box is selected, VBA moves the insertion point to the temporary bookmark and then deletes that bookmark.
Listing 14.1 shows the full listing for the cmdOK button.
Go ahead and try it. To test this example properly, you should remove the bookmark you inserted while recording the macro earlier in this chapter. To remove it, click the Bookmark item in the Links section in the Insert tab on Word's Ribbon. In the Bookmarks dialog box that opens, select Move_Paragraph_Temp
and click the Delete button.
Now, open the scratch document in Word that you created earlier in this chapter and filled with several paragraphs of text:
frmMoveParagraph
in the Project Explorer to display the user form.This next example displays a user form that employs a list box from which the user can select a file to open. The user form is simple, as is its code. The macro includes a loop and an array to gather the names of the files in a folder and then displays the filenames in the list box. The user gets to select a file and click the Open button to open it. Figure 14.21 shows the user form in action, displaying Excel files.
You can adapt this example to any of the Office 2019 applications discussed in this book by changing the filename to an appropriate type for that application and also modifying a couple of the key statements. The version of this example we'll look at now shows you how to create the procedure in Excel.
Follow these steps to build the user form:
Name
property of the form to frmOpen_a_Current_File
and its Caption
to Open a Current File
. Check the Width
property. You want it to be about 350 pixels wide.PROPERTY | VALUE |
(Name) |
lblInfo |
AutoSize |
True |
Caption |
Choose the file to open and click the Open button. |
Left |
10 |
Top |
6 |
WordWrap |
False |
PROPERTY | VALUE |
(Name) |
lstifles |
Height |
100 |
Left |
10 |
Top |
25 |
Width |
300 |
PROPERTY | FIRST BUTTON VALUE | SECOND BUTTON VALUE |
(Name) |
cmdOpen |
cmdCancel |
Cancel |
False |
True |
Caption |
Open |
Cancel |
Default |
True |
False |
Height |
21 |
21 |
Width |
55 |
55 |
Follow these steps to create the code for the user form:
Option Base 1
statement to make the array numbering start at 1
instead of at 0
:
Option Base 1
The Visual Basic Editor enters the stub of an Initialize
procedure in the code sheet, like this:
Private Sub UserForm_Initialize()
End Sub
Initialize
procedure shown in Listing 14.2.The Visual Basic Editor enters the stub of a Click
procedure, as shown here. (Click
is the default event for the CommandButton control, so the Visual Basic Editor assumes that you want to create a Click
procedure.)
Private Sub cmdCancel_Click()
End Sub
cmdCancel_Click
procedure shown in Listing 14.2.Click
procedure.cmdOpen_Click
procedure shown in Listing 14.2.Initialize
procedure) and line 32 (in the cmdOpen_Click
procedure) so that the code will work with the application you're using, as shown in the following list. The procedure as shown is set up to run for Excel, but you'll probably need to change the path to reflect where the target files are on your computer.
Workbooks.Open
statement to Documents.Open
:
If lstFiles.Value <> "" Then Documents.Open _
Filename:="c: ransfer" & lstFiles.Value
Workbooks.Open
statement to Presentations.Open
:
If lstFiles.Value <> "" Then Presentations.Open _
Filename:="c: ransfer" & lstFiles.Value
Listing 14.2 shows the full version of the code behind the Open a Current File user form.
Listing 14.2 contains all the code that appears on the code sheet for the frmOpen_a_Current_File
user form: a declarations section and three event procedures.
In the declarations section, line 1 contains the Option Base 1
statement, which makes any array used on the code sheet begin at 1
rather than at 0
. Line 2 is a spacer.
Here's what happens in the UserForm_Initialize
procedure (lines 3 to 23):
Initialize
procedure for the user form.strFileArray
.strFFile
.intCount
.strFFile
the result of a directory operation on the designated folder (here, c: ransferspreads
), but substitute your own path to a folder on your computer that contains files with an .xlsb
filename extension. Enter your own path in line 32 as well.intCount
counter to 1
.
Note that if you don't use the Option Base 1
declaration for this procedure, you need to set Count
to 0
(or the corresponding value for a different option base that you use). The first call to Dir
, which specifies the pathname in an argument, returns the first file it finds in the folder (assuming it finds at least one file). Each subsequent call without the argument returns the next file in the folder, until Dir
finds no more files.
Do While…Loop
loop that runs while strFFile
isn't an empty string (″″
):
strFFile
isn't a folder by comparing it to the single period and double period used to denote folders. If strFFile
isn't a folder, line 14 uses a ReDim Preserve
statement to increase the dimensions of the strFileArray
array to the number in intCount
while retaining the current information in the array, thus building the list of files in the folder.intCount
index of the strFileArray
array the current contents of strFFile
.1
to intCount
, and line 17 sets strFFile
to the result of the Dir
function (the first filename matching the *.xlsb
pattern in the designated folder).If
condition. Line 19 contains the Loop
keyword that will continue the loop as long as the Do While
statement is True
.List
property of the lstFiles
list box in the dialog box to the contents of strFileArray
, which now contains a list of all the files in the folder.Here's what happens in the cmdCancel_Click
procedure (lines 25 through 28):
cmdCancel_Click
procedure, and line 28 ends it.Me
keyword to reference it.Here's what happens in the cmdOpen_Click
procedure (lines 30 through 34):
cmdOpen_Click
procedure, and line 34 ends it.Me
keyword.Value
property of the lstFiles
list box is not an empty string (""
) and, if it is not, uses the Open
method of the Documents
collection to open the file selected in the list box. The statement adds to the path (c: ransferspreads
) the Value
property of the list box to produce the full filename. Substitute your own path for c: ransferspreads
.Remember that to test this example, you'll need to adjust lines 9 and 32 to include a file path on your machine where some XLSB files are stored. For Excel 2019, try this location: C:UsersYourNameAppDataRoamingMicrosoftExcelXLSTART.
Some applications, such as Word and Excel, let you use their built-in dialog boxes via VBA. If a built-in dialog box offers the functionality you need, using it can be a great solution: You don't have to build a custom dialog box, just reference the built-in dialog box in your code.
You shouldn't even need to debug a built-in dialog box, and users of your procedures will probably be familiar with the dialog box from their work in the application. These built-in dialog boxes are called common dialog boxes, and we explored them briefly in the sidebar titled “Control a For…Next
Loop with User Input via a Dialog Box” in Chapter 12.
To display a built-in dialog box, you need to know its name and constant. You also must decide which method to use to display the dialog box.
Although Office 2019 no longer uses menus (with some exceptions, such as the Visual Basic Editor), built-in dialog boxes (in Word and other applications) are still identified by constants derived from the older, pre-Ribbon menu-style interface. These constants start with the letters wdDialog
(as in Word dialog), followed by the name of the dialog box.
The names of common dialog boxes are derived from the pre–Office 2010 menu commands that displayed the dialog boxes prior to the introduction of the Ribbon interface (with Office 2007). For example, to refer to the Open dialog box, you use the constant wdDialogFileOpen
, because in previous versions of Word, you would have chosen File ➢ Open to display that dialog box.
Or to display the Print dialog box (the old File ➢ Print options), you use the constant wd DialogFilePrint
, and to display the Options dialog box (Tools ➢ Options), you use the constant wdDialogToolsOptions
.
So, although the user interface has evolved beyond classic menus, the menu structure itself remains as part of the classification system for internal objects—such as these constants used to identify various dialog boxes.
Excel follows a similar but less rigid taxonomic convention. Built-in Excel dialog boxes are (for backward compatibility with older macro code) still identified by constants starting with the letters xlDialog
followed by the name of the dialog box. The name of the dialog box is derived either from the classic menu commands that were required to display it or from the dialog box's title. For example, to refer to the Open dialog box, you use the constant xlDialogOpen
(rather than xlDialogFileOpen
).
Anyway, the easiest way to find the name for the built-in dialog box you need is to search the Visual Basic Editor's Help system for “Built-In Dialog Box Argument Lists” in Word or Excel. (Access employs a whole different system for common dialog boxes, requiring the importation of object libraries using its Visual Basic Editor's Tools ➢ References menu and the employment of specialized objects.)
You can also view a list of Word or Excel built-in dialog boxes by displaying the Object Browser (press F2 in the Editor) and typing wddialog
(for Word) or xldialog
(for Excel) in the Search textbox.
You use these constants with the Dialogs
property, which returns the Dialogs
collection object, which in turn contains all the built-in dialog boxes in the host application.
For example, to display Word's Save As dialog box, you use the Show
method, as illustrated in the following statement:
Dialogs(wdDialogFileSaveAs).Show
It's as simple as that. To display Word's Replace dialog box, just substitute wdDialogEdit Replace
for wdDialogFileSaveAs
.
VBA provides two methods of displaying built-in dialog boxes onscreen: Show
and Display
:
Show
method shows the specified Dialog
object and then uses functions built into the Dialog
object to carry out the user's requests.
You don't need to write any code of your own. For example, if you use the Show
method to display the wdDialogFileSaveAs
dialog box. It's fully functional when shown this way, so the user can just enter a name for the file in the File Name box and click the Save button. VBA itself automatically saves the file with the given name in the specified folder (and with any other options the user chose). You didn't write any programming to save this file.
Display
method merely displays the dialog box onscreen, but it does not execute the actions the user requests in the dialog box. Instead, it allows you to fetch the settings (the user's requests and selections) from the dialog box once the user dismisses it, but then you must write your own code to carry out what the user requested. This is a way to intercept user input and employ it to suit abnormal purposes.The Show
method displays the specified dialog box and automatically responds to whatever actions the user takes in it. Show
is useful when your user is merely going to perform a conventional interactive action. As a simple example, in a procedure that's supposed to perform certain formatting tasks on the current document, you could check to make sure a document is open before attempting to perform the formatting. Then, if no document is open, you could display the built-in Open dialog box so that the user could open a file. (You might precede the Open dialog box with a message box explaining the problem.) Listing 14.3 shows the code for this part of the procedure.
Here's how the code works:
Count
property of the Documents
collection to see if no documents are open. If that's the case, the statements in lines 2 through 8 run.Proceed
.Count
property of the Documents
collection again and uses an End
statement to terminate execution of the procedure if there is still no document open.Else
statement in line 6, and the End
statement in line 7 ends execution of the procedure.End If
statement for the nested If
statement, and line 9 contains the End If
statement for the outer If
statement.Remember that unlike the Show
method, the Display
method displays a built-in dialog box but doesn't itself respond to any actions the user takes in the dialog box. Instead, you must write code that checks the settings that the user chose in the dialog box and then write more code to carry out the user's wishes. When you use the Display
method, the user gets to work with familiar dialog boxes, but you totally control the behavior that results from that interaction.
For example, you'll often need to find out which folder a procedure should be working in, such as when you need the location of a number of documents that the user wants to manipulate. To get the folder, you could display a straightforward input box and prompt the user to type in the correct path to the folder—if the user knows the path and can type it in correctly.
Perhaps a better solution is to display a list box containing the tree of drives, folders, and files on the user's hard drive, but to do this you need to dimension an array and fill it with the folders and filenames, and you need to refresh the display every time the user moves up or down the tree—quite a lot of programming work.
So why not just borrow all this functionality from a built-in common dialog box? It's already part of the Office applications. You can achieve the same result much more easily by using a built-in dialog box that has the tree built in (for example, the Open dialog box) and then retrieving the user's responses for your own purposes.
If you need to execute the settings (user choices) in a built-in dialog box, you can use the Execute
method. But you might want to check the user's selections in the dialog box before implementing them. If you find a problem, you could then, for example, display a dialog box of your own, such as an input box, asking for clarification.
Most of the built-in Word and Excel dialog boxes have arguments that you can use for retrieving or setting values in the dialog box. For example, the Open dialog box in Word has arguments for Name
, ConfirmConversions
, ReadOnly
, LinkToSource
, AddToMru
(adding the document to the Most Recently Used document list on the Recent section of the File tab on the Ribbon), PasswordDoc
, and more. Some of these are options that you'll see in the Open dialog box itself. Others are associated options that you'll find on the various tabs of the Options dialog box.
You can guess some argument names from the names of the corresponding controls in the dialog box, but other names aren't directly related. To learn the names, search for “Built-In Dialog Box Argument Lists” in the VBA Editor's Help system (choose MSDN on the Web, and then search with Bing).
For example, the following statements set the contents of the File Name text box in the Save As dialog box in Word and then display the dialog box:
With Dialogs(wdDialogFileSaveAs)
.Name = "Yellow Paint Primer"
.Show
End With
Be aware that some arguments that applied to dialog boxes displayed by Office 2003 are not used in later versions of Office dialog boxes. So, you may need to experiment a bit to see if a particular legacy argument is still useful in the Office 2019 interface.
If you change the settings in a dialog box that uses sticky (persistent) settings, it's a good idea to change them back at the end of your procedure so that users don't get unexpected results the next time they open the dialog box.
To find out which button the user clicked in a dialog box, check the return value of the Show
method or the Display
method. The return values are shown in Table 14.12.
TABLE 14.12: Click return values
RETURN VALUE | BUTTON CLICKED |
–2 |
Close |
–1 |
OK |
0 |
Cancel |
1 |
The first command button |
2 |
The second command button |
>2 (greater than 2) |
Subsequent command buttons |
For example, you might want to cancel your whole procedure if the user clicks the Cancel button in a dialog box, like this:
If Dialogs(wdDialogFileOpen).Show = 0 Then End
In some applications, including Word, you can display some built-in dialog boxes for a specified time rather than having them stay open until the user dismisses them by clicking OK or Cancel or some other button. To specify a duration, you use the TimeOut
Variant argument with the Show
method or the Display
method. You specify TimeOut
as a number of units, each of which is approximately a thousandth of a second. (If the system is busy with many other tasks, the actual result might be a slightly longer delay.) So, you could display the General page of the Word Options dialog box for about 10 seconds—long enough for the user to check the Name setting and change it if necessary—by using the following statements:
With Dialogs(wdDialogToolsOptions)
.DefaultTab = wdDialogToolsOptionsTabUserInfo
.Show (10000)
End With
Dialog boxes often display objects with which users can communicate their wants to your code. Therefore, you write code that reads the values the user enters into controls, and responds.
optSmall
, optMedium
, and optLarge
. Write code in each option button's Click
procedure to change the button's caption to boldface when the button is clicked.