Designing a Dialog Box

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).

Inserting a User 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:

1. Click Developer ➢ Visual Basic to display the Visual Basic Editor if it's not already open.
2. In the Project Explorer window, right-click the appropriate project and choose Insert ➢ UserForm from the context menu.

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.

Choosing User-Form Grid Settings

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:

1. Choose Tools ➢ Options to display the Options dialog box.
2. Click the General tab to display the General page (see Figure 14.2).

   

3. Choose the settings you want:
   1. Clear the Show Grid check box if you want to turn off the display of the grid. (The grid continues to function, but the dots are not displayed.)
   2. Clear the Align Controls To Grid check box if you want to stop using the grid whether it's visible or not. This feature is usually a timesaver, but if the grid is too coarse for the layout you're trying to achieve, just reduce the sizing of the grid from the default 6 to perhaps 3 or 4.
   3. Change the number of points in the Width and Height text boxes to adjust the sizing of the grid's units.
4. Click the OK button to close the Options dialog box and apply your choices.

Renaming a User Form

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.)

1. If the Properties window isn't displayed, press F4 to display it.

   Figure 14.3 shows the two pages of the Properties window: Alphabetic and Categorized.

   • Alphabetic displays an alphabetical listing of the properties of the currently selected object.
   • Categorized displays the same properties but separated into categories, such as Appearance, Behavior, Font, Misc., Picture, and Position. (Some controls have more categories than those listed here.)

   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.

   

2. If the Alphabetic tab isn't selected, click it to select it.

   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.

3. Make sure the drop-down list (at the top of the Properties window) is displaying the default name of the user form. If it isn't, select the user form from the drop-down list.
4. Select the user form's default name (such as UserForm1 or UserForm2) in the cell to the right of the Name cell (you can double-click the name to select it quickly).
5. Now type a new, more descriptive name for the user form.

   This name can be anything you want, with the standard VBA limitations:

   • It must start with a letter.
   • It can contain letters, numbers, and underscores but no spaces or symbols.
   • It can be up to 40 characters long.
6. Click the Caption cell to select the user form's default name and type the caption for the user form—that is, the text label that you want the user to see in the title bar of the dialog box.

   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.

7. Press Enter or click elsewhere in the Properties window (or elsewhere in the Visual Basic Editor) to set (make official) the user form's name. (Assigning names to controls works the same way as assigning names to forms.)

Adding Controls to 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:

• Select Objects This first control, the arrow symbol, has a very specialized purpose, and you might never need to use it. First, it's not an ordinary control (it doesn't appear on a form; you can't drag and drop it onto a form). Its job is to restore the mouse pointer to Selection mode. However, the mouse pointer automatically returns to Selection mode after you've dropped a control onto a form. Usually, you'll need to click this Select Objects button only when you've selected another control and then changed your mind and decided not to use it, so you need to restore the pointer to its normal state.

  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.

• Label Creates a label, which is text used to identify a part of the dialog box or to explain information the user needs to know in order to use the dialog box effectively.
• TextBox Creates a text box (also sometimes called an edit box), a field into which the user can type text. You can also use a text box to display text to the user or to provide text for the user to copy and paste elsewhere. A text box can contain either one line (the default) or multiple lines and can display a horizontal scroll bar, a vertical scroll bar, or both.
• ComboBox Creates a combo box, a control that combines a text box with a list box. The user can either choose a value from the list box or enter a new value in the text box.
• ListBox Creates a list box, a control that lists a number of values. Users can pick one value from the list but can't enter a new value of their own (unlike with a combo box). The list box is good for presenting closed sets of data.
• CheckBox Creates a check box and an accompanying label. The user can select or clear the check box to turn the associated action on or off.
• OptionButton Creates an option button (also known as a radio button) and an accompanying label to identify the purpose of the button. This button is usually a circle that contains a black dot to show which of several options is currently selected. The user can select only one option button out of any group of option buttons. (The name radio button comes from old fashioned radios with push buttons for stations. You can select only one button at a time. Push one, and the others pop out.)
• ToggleButton Creates a toggle button, a button that shows whether or not an item is selected. A toggle button can be defined with any two settings, such as On/Off or Yes/No. You can add a picture to a toggle button, which provides a graphical way of letting a user choose between options.
• Frame Creates a frame, an area of a user form or dialog box surrounded by a thin line and an accompanying label. You can use frames (also known as group boxes) to group related elements in your forms. As well as cordoning off elements visually, frames can separate elements logically. For example, VBA treats a group of option buttons contained within a frame as separate from option buttons in other frames or option buttons loose in the dialog box. This separation makes it easier to use multiple sets of option buttons in a form.
• CommandButton Creates a command button. This is the typical, ordinary Windows button that users click to communicate their wishes. Most dialog boxes contain command buttons such as OK and Cancel, or Open and Cancel, or Save, or Apply and Close.
• TabStrip Creates a tab strip for displaying multiple sets of data in the same set of controls. Tab strips are especially useful for presenting records in a database for review or modification: Each record in the database contains the same fields for information, so they can be displayed in the same group of controls. The tab strip provides an easy way of navigating between records.
• MultiPage Creates a multipage control for displaying multipage dialog boxes that have different layouts on each of their tabs. An example of a multipage dialog box is the Options dialog box (Tools ➢ Options), which has multiple pages (often referred to incorrectly as tabs) in most of the Office applications.
• ScrollBar Creates a stand-alone scroll bar. Stand-alone scroll bars are of relatively little use in dialog boxes. Combo boxes and list boxes have built-in scroll bars.
• SpinButton Creates a spin-button control to use along with another control, such as a text box. Spin buttons (also known as spinners) are typically small, rectangular buttons with one arrow pointing up and one down (or one arrow pointing left and the other pointing right). Spin buttons are useful for presenting sequential values with consistent intervals that fall between a predictable range, such as times or dates. For example, if you want the user to increment or decrement a price in a text box in 25-cent steps, you could use a spinner to adjust the price rather than letting the user type directly into the text box.
• Image Use this control to display a picture on a form. For example, you might use an image control to show a corporate logo. (If you want to display a photo, texture, or other graphic on the background of the form itself, just use the form's Picture property.)

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.

Grouping Controls

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:

• To select multiple contiguous controls, click the first control, hold down Shift, and then click the last control in the sequence.
• To select multiple noncontiguous controls—or to add additional controls to a group after you've selected multiple contiguous controls by using the Shift key—hold down the Ctrl key as you click each additional control. (With the Ctrl key pressed, you can deselect any control in a group by clicking it a second time.)
• To select multiple controls in the same area of the user form, click in the form's background outside the controls and drag the resulting selection box frame until it encompasses at least part of each control. When you release the mouse button, the Visual Basic Editor selects the controls as a group.

Renaming 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:

1. Click the control in the user form to select it and thereby display its properties in the Properties window.
   • If the Properties window is already displayed, you can, if you prefer, select the control from the drop-down list at the top of the Properties window instead of selecting it in the user form. VBA then visually highlights (selects) the control in the user form, which helps you make sure that you've selected the control you want to affect.
   • If the Properties window isn't displayed, you can quickly display it with the properties for the appropriate control by right-clicking the control in the user form and choosing Properties from the context menu. Or just press F4. (If the Toolbox disappears, just click the form to bring it back.)
2. In the Properties window, double-click to select the default name in the cell to the right of the Name property.
3. Type the new name for the control.
4. Press Enter to set the control name, or click elsewhere in the Properties window or in the user form.

Moving a Control

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.

Changing the Caption on a Control

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:

1. Click the control to select it.
2. Click the caption itself to select it.

   VBA displays the blinking insertion cursor and a faint dotted border around the text, as shown in Figure 14.9.

   

3. Now click in the label to position the insertion point for editing it, or drag through the label to select all of it.
4. Edit the text of the label as desired.
5. Press Enter or click elsewhere in the user form to effect the change to the label. (You can alternatively change the label by changing its Caption property in the Properties window.)

Key Properties of the Toolbox Controls

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.

CHECKBOX

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:

• The 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.
• You can also choose 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

  

• The 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.

How to Group Controls

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.

Ungrouping Controls

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.

Sizing Grouped Controls

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.

Deleting Grouped Controls

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.

Working with One Control in a Group

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.

Aligning Controls

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:

• Lefts aligns the left borders of the controls.
• Centers aligns the horizontal midpoints of the controls.
• Rights aligns the right borders of the controls.
• Tops aligns the tops of the controls.
• Middles aligns the vertical midpoints of the controls.
• Bottoms aligns the bottoms of the controls.
• To Grid aligns the controls to the grid.

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.

Placing Controls

The VBA Editor offers several placement commands on the Format menu:

• On the Format ➢ Make Same Size submenu, use the Width, Height, and Both commands to make two or more controls the same size in one or both dimensions.
• Use the Format ➢ Size To Fit command to have VBA decide on a suitable size for an element based on the size of its label. This works well for, say, a toggle button with a medium-length label, but VBA will shrink an OK button to a size so small as to be unusable.
• Use the Format ➢ Size To Grid command to increase or decrease the size of a control to the nearest gridpoints.
• On the Format ➢ Horizontal Spacing and Format ➢ Vertical Spacing submenus, use the Make Equal, Increase, Decrease, and Remove commands to set the horizontal spacing and vertical spacing of two or more controls. The Remove option removes extra space from between controls, which works well for, say, a vertical series of option buttons (which look good close together) but isn't a good idea for command buttons (which need a little space between them).
• On the Format ➢ Center In Form submenu, use the Horizontally and Vertically commands to center a control or a group of controls in the form. Centering controls vertically is seldom a good idea, but you'll often want to center a frame or a group of command buttons horizontally.
• On the Format ➢ Arrange Buttons submenu, use the Bottom and Right commands to reposition command buttons in a form quickly.

Adjusting the Tab Order of a Form

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:

1. Rearrange the controls into the order in which you want them to appear by selecting them in the Tab Order list box and clicking the Move Up button or Move Down button as appropriate.

   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.)

2. Click the OK button to close the Tab Order dialog box.

Loading and Unloading a Form

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

Displaying and Hiding a Form

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.

Setting a Default Command Button

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.

Returning a String from a Text Box

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).

Returning a Value from an Option Button

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.

Returning a Value from a Check Box

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.

Returning a Value from a List Box

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:

1. Right-click the name of the user form in the Project Explorer and choose View Code from the context menu to display (in the Code window) the code for the controls assigned to the dialog box.

   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).

2. In the Object drop-down list (on the top left of the Code window), make sure UserForm is selected.
3. Choose Initialize from the Procedure drop-down list (on the top right of the Code window).

   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.

4. To add items to a list box, use the 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

Returning a Value from a Combo Box

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.

Word Example: The Move-Paragraph Procedure

This first example moves the current paragraph up or down within the document by one or two paragraphs in Word.

CREATING THE DIALOG BOX

Next, create the dialog box for the procedure (see Figure 14.16):

1. Start a user form in the Visual Basic Editor by clicking the Insert button's drop-down list and choosing UserForm (or just click the Insert button if it's already showing the UserForm icon) or by choosing Insert ➢ UserForm.
2. Use the Properties window for the user form to set its Name and Caption properties:
   1. Click in the cell next to the Name cell and enter the Name property there.
   2. Click in the cell next to the 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).
3. Place two frames in the user form, as shown in Figure 14.17, to act as group containers in the dialog box:
   1. Double-click the Frame tool in the Toolbox, and then click and drag in the user form to place each frame.
   2. Align the frames by selecting them both and choosing Format ➢ Align ➢ Lefts.
   3. With the frames still selected, verify that they are the same width by choosing Format ➢ Make Same Size ➢ Width. (Don't choose Format ➢ Make Same Size ➢ Height or Format ➢ Make Same Size ➢ Both. The top frame will need to be taller than the bottom frame.)
   4. Caption the top frame 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.

   

4. Place four option buttons in the Movement frame, as shown in Figure 14.18:
   1. Double-click the OptionButton tool in the Toolbox, and then click in the Movement frame to place each option button. This time, don't click and drag—just click to place a normal-width option button.
   2. When you've placed the four option buttons, click the Select Objects button in the Toolbox to restore the selection pointer.
   3. Then select the four option buttons and align them with each other by choosing Format ➢ Align ➢ Lefts.
   4. Even out any disparities in spacing by choosing Format ➢ Vertical Spacing ➢ Make Equal. If necessary, use the other items on the Format ➢ Vertical Spacing submenu—Increase, Decrease, and Remove—to adjust the amount of space between the option buttons.

      You can do all these things freehand if you prefer by just eyeballing. Drag them around until you have them neatly positioned and sized.

   5. Change the caption for each option button by setting the 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.

   6. If you need to resize the option buttons to make all the text in the captions visible, select them and group them by right-clicking and choosing Group from the context menu, by choosing Format ➢ Group, or by clicking the Group button on the UserForm toolbar.
   7. Then select the group and drag one of the handles to resize all the option buttons evenly.

      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.

   8. Name the option buttons optUpOne, optUpTwo, optDownOne, and optDownTwo, respectively, by changing the Name property of each in turn in the Properties window.
   9. Next, set the first option button's 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.

   10. Set its accelerator key to U by entering 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.

   

5. Place a check box in the Insertion Point frame, as shown in Figure 14.19:
   1. Click the CheckBox tool in the Toolbox and then click in the Insertion Point frame in the user form to place a check box of the default size.
   2. In the Properties window, set the name of the check box to chkReturnToPrevious Position (a long name but a descriptive one).
   3. Then set its Caption property to Return to previous position.
   4. Set its accelerator key to R by entering R as its Accelerator property.
   5. Finally, set the check box to be selected by default by entering True as its Value property.

   

6. Next, insert the command buttons for the form (see Figure 14.20):
   1. Double-click the CommandButton tool on the Toolbox and click to place the first command button at the bottom of the user form.
   2. Click to place the second command button, and then click the Select Objects button to restore the selection mouse pointer.
   3. Size and place the command buttons by using the commands on the Format menu. For example, group the buttons, and then use the Format ➢ Center In Form ➢ Horizontally command to center the pair horizontally. You must group the buttons before doing this. If instead you simply select both of them, VBA centers one button on top of the other so that only the uppermost button is visible.
   4. Set properties of the command buttons as follows:
      • For the left-hand button (which will become the OK button), set the Name property to cmdOK.
      • Set the Caption property to OK.
      • Set the Accelerator property to O (that's O as in OK, not a zero).
      • Set the Default property to True.
      • For the right-hand button (which will become the Cancel button), set the Name property to cmdCancel.
      • Set the Accelerator property to A.
      • Set the Caption property to Cancel.
      • Set the Cancel property to True.
      • Leave the Default property set to False.

   

7. Now we attach our code to this form. Dive down into the Code window by double-clicking the Cancel button to display a procedure associated with it:

           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:

   • Remove the dialog box from display by hiding it or by unloading it (or, preferably, both). As discussed earlier in the chapter, the choice is yours, but using both commands is usually clearest.
   • Check the Value property of the check box to see whether it was selected or cleared.
   • Check the 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:

8. Double-click the OK button to display the code attached to it.

   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.

9. First, enter the following two lines between the 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.

10. Next, the procedure needs to check the 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.

11. Cut these lines from the procedure and paste them into the 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.

12. Next, right after the 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

13. After this, you need to retrieve the 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.

14. Finally, the procedure must return the insertion point to where it was originally if the 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:

1. Click Developer ➢ Visual Basic to open the Visual Basic Editor.
2. Double-click frmMoveParagraph in the Project Explorer to display the user form.
3. Press F5 to run this procedure.
4. Click the OK button in your user form and observe that the paragraphs were rearranged in the document.

General Example: Opening a File from a List Box

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.

Building the User Form

Follow these steps to build the user form:

1. Start the application you want to work in. The example uses Excel.
2. Display the Visual Basic Editor by clicking the Visual Basic icon in the Ribbon's Developer tab.
3. In the Project Explorer, right-click the project to which you want to add the user form and choose Insert ➢ UserForm from the context menu to insert a default-size user form in the project.
4. Drag the handle at the lower-right corner of the user form to the right to make the user form a bit wider.
5. Set the 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.
6. Click the Label button in the Toolbox, and then click in the upper-left corner of the user form to place a default-size label there.
7. Activate the Properties window and set the properties of the label as shown in Table 14.9.

   PROPERTY	VALUE
   (Name)	lblInfo
   AutoSize	True
   Caption	Choose the file to open and click the Open button.
   Left	10
   Top	6
   WordWrap	False

8. Click the ListBox button in the Toolbox, and then click below the label in the user form to place a default-size list box there.
9. Set its properties as shown in Table 14.10.

   PROPERTY	VALUE
   (Name)	lstifles
   Height	100
   Left	10
   Top	25
   Width	300

10. Double-click the CommandButton button in the Toolbox, and then click twice at the bottom of the user form to place two default-size command buttons there.
11. Set their properties as shown in Table 14.11.

    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

12. Arrange the command buttons as follows:
    1. Click the cmdCancel button to select it, and then drag it close to the cmdOK button.
    2. With the cmdCancel button still selected, Ctrl+click the cmdOK button to add it to the selection.
    3. Choose Format ➢ Group to group the buttons.
    4. Choose Format ➢ Center In Form ➢ Horizontally to center the buttons horizontally in the form.
    5. Drag the group up or down as necessary. (Or just drag them around and eyeball them into a pleasing position.)

Creating the Code for the User Form

Follow these steps to create the code for the user form:

1. With the user form selected, press the F7 key to display the user form's code sheet.
2. In the declarations portion of the code sheet (just keep pressing the up-arrow key until you move to the very top of the Code window), enter an Option Base 1 statement to make the array numbering start at 1 instead of at 0:

    Option Base 1

3. Make sure that UserForm is selected in the Object drop-down list (top left of the code sheet), and then pull down the Procedure drop-down list (top right) and choose Initialize from it.

   The Visual Basic Editor enters the stub of an Initialize procedure in the code sheet, like this:

           Private Sub UserForm_Initialize()
    
           End Sub

4. Enter the statements for the Initialize procedure shown in Listing 14.2.
5. In the Object drop-down list, select cmdCancel.

   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

6. Enter the statements for the cmdCancel_Click procedure shown in Listing 14.2.
7. In the Object drop-down list, select cmdOpen. The Visual Basic Editor enters the stub of a Click procedure.
8. Enter the statements for the cmdOpen_Click procedure shown in Listing 14.2.
9. Customize line 9 (in the 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.
   • For Word, change the Workbooks.Open statement to Documents.Open:

             If lstFiles.Value <> "" Then Documents.Open _
                 Filename:="c:	ransfer" & lstFiles.Value

   • For PowerPoint, change the 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):

• Line 3 begins the Initialize procedure for the user form.
• Line 4 is a spacer.
• Line 5 declares the String array variable strFileArray.
• Line 6 declares the String variable strFFile.
• Line 7 declares the Integer variable intCount.
• Line 8 is a spacer.
• Line 9 assigns to 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.
• Line 10 sets the 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.

• Line 11 is a spacer.
• Lines 12 through 19 contain a Do While…Loop loop that runs while strFFile isn't an empty string (″″):
  • Line 13 makes sure that 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.
  • Line 15 assigns to the intCount index of the strFileArray array the current contents of strFFile.
  • Line 16 then adds 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).
  • Line 18 ends the If condition. Line 19 contains the Loop keyword that will continue the loop as long as the Do While statement is True.
• When the loop ends, line 21 sets the 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.
• Line 22 is a spacer.
• Line 23 ends the procedure, and line 24 is another spacer.

  Here's what happens in the cmdCancel_Click procedure (lines 25 through 28):

  • Line 25 starts the cmdCancel_Click procedure, and line 28 ends it.
  • Line 26 hides the user form, using the Me keyword to reference it.
  • Line 27 unloads the user form from memory.

  Here's what happens in the cmdOpen_Click procedure (lines 30 through 34):

  • Line 30 starts the cmdOpen_Click procedure, and line 34 ends it.
  • Line 31 hides the user form, again by using the Me keyword.
  • Line 32 checks to make sure the 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.
  • Line 33 unloads the user form from memory.

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.

Displaying a Built-In Dialog Box

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.

Setting and Restoring Options in a Built-In Dialog Box

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.

Which Button Did the User Choose in a 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.

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

Specifying a Time-Out for a Dialog Box

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