You can’t use Excel very long without being exposed to dialog boxes. Excel, like most Windows programs, uses dialog boxes to obtain information, clarify commands, and display messages. If you develop VBA macros, you can create your own dialog boxes that work very much like those that are built into Excel. These dialog boxes are known as UserForms.
Some macros that you create behave exactly the same every time that you execute them. For example, you may develop a macro that enters a list of your sales regions into a worksheet range. This macro always produces the same result and requires no additional user input. You may develop other macros, however, that perform differently under different circumstances or that offer options for the user. In such cases, the macro may benefit from a custom dialog box.
The following is an example of a simple macro that makes each cell in the selected range uppercase (but it skips cells that have a formula). The procedure uses VBA’s built-in StrConv
function.
Sub ChangeCase() For Each cell In Selection If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbUpperCase) End If Next cell End Sub
This macro is useful, but it can be improved. For example, the macro would be more helpful if it could also change the cells to lowercase or proper case (only the first letter of each word is uppercase). This modification is not difficult to make, but if you make this change to the macro, you need some method of asking the user what type of change to make to the cells. The solution is to present a dialog box like the one shown in Figure 41.1. This dialog box is a UserForm that was created by using the Visual Basic Editor, and it is displayed by a VBA macro.
Another solution is to develop three macros, one for each type of text case change. Combining these three operations into a single macro and using a UserForm is a more efficient approach, however. I discuss this example, including how to create the UserForm, in “Another UserForm Example,” later in the chapter.
Once you get the hang of it, developing UserForms isn’t difficult. But sometimes using the tools that are built into VBA is easier. For example, VBA includes two functions (InputBox
and MsgBox
) that enable you to display simple dialog boxes without having to create a UserForm in the VB Editor. You can customize these dialog boxes in some ways, but they certainly don’t offer the number of options that are available in a UserForm.
The InputBox
function is useful for obtaining a single input from the user. A simplified version of the function’s syntax follows:
InputBox(prompt[,title][,default])
The elements are defined as follows:
prompt:
Text that is displayed in the input box. (Required)
title:
Text that appears in the input box’s title bar. (Optional)
default:
The default value. (Optional)
The following is an example of how you can use the InputBox
function:
CName = InputBox("Customer name?","Customer Data")
When this VBA statement is executed, Excel displays the dialog box shown in Figure 41.2. Notice that this example uses only the first two arguments for the InputBox
function and does not supply a default value. When the user enters a value and clicks OK, the value is assigned to the variable CName
. Your VBA code can then use that variable.
VBA’s MsgBox
function is a handy way to display information and to solicit simple input from users. I use VBA’s MsgBox
function in many of this book’s examples to display a variable’s value. A simplified version of the MsgBox
syntax is as follows:
MsgBox(prompt[,buttons][,title])
The elements are defined as follows:
prompt:
Text that is displayed in the message box. (Required)
buttons:
The code for the buttons that are to appear in the message box. (Optional)
title:
Text that appears in the message box’s title bar. (Optional)
You can use the MsgBox
function by itself or assign its result to a variable. If you use it by itself, don’t include parentheses around the arguments. The following example displays a message and does not return a result:
Sub MsgBoxDemo() MsgBox "Click OK to continue" End Sub
Figure 41.3 shows how this message box appears.
To get a response from a message box, you can assign the result of the MsgBox
function to a variable. The following code uses some built-in constants (described in the “Constants That Are Used in the MsgBox Function” sidebar) to make it easier to work with the values that are returned by MsgBox
:
Sub GetAnswer() Ans = MsgBox("Continue?", vbYesNo) Select Case Ans Case vbYes ' ...[code if Ans is Yes]... Case vbNo ' ...[code if Ans is No]... End Select End Sub
When this procedure is executed, the Ans
variable contains a value that corresponds to vbYes
or vbNo
. The Select Case
statement determines the action to take based on the value of Ans
.
You can easily customize your message boxes because of the flexibility of the button
argument. Table 41.1 lists the built-in constants that you can use for the button
argument. You can specify which buttons to display, whether an icon appears, and which button is the default.
Table 41.1. Constants That Are Used in the MsgBox Function
Constant | Value | Description |
---|---|---|
|
| Display OK button. |
|
| Display OK and Cancel buttons. |
|
| Display Abort, Retry, and Ignore buttons. |
|
| Display Yes, No, and Cancel buttons. |
|
| Display Yes and No buttons. |
|
| Display Retry and Cancel buttons. |
|
| Display Critical Message icon. |
|
| Display Warning Query icon. |
|
| Display Warning Message icon. |
|
| Display Information Message icon. |
|
| First button is default. |
|
| Second button is default. |
|
| Third button is default. |
The following example uses a combination of constants to display a message box with a Yes button, a No button (vbYesNo
), and a question mark icon (vbQuestion
); the second button is designated as the default button (vbDefaultButton2
)—which is the button that is executed if the user presses Enter. For simplicity, these constants are assigned to the Config
variable, and Config
is then used as the second argument in the MsgBox
function.
Sub GetAnswer() Config = vbYesNo + vbQuestion + vbDefaultButton2 Ans = MsgBox("Process the monthly report?", Config) If Ans = vbYes Then RunReport If Ans = vbNo Then Exit Sub End Sub
Figure 41.4 shows how this message box appears when the GetAnswer
Sub is executed. If the user clicks the Yes button (or presses Enter), the routine executes the procedure named RunReport
(which is not shown). If the user clicks the No button, the procedure is ended with no action. Because the title argument was omitted in the MsgBox
function, Excel uses the default title ("Microsoft Excel"
).
The Sub procedure that follows is another example of using the MsgBox
function:
Sub GetAnswer2() Msg = "Do you want to process the monthly report?" Msg = Msg & vbNewLine & vbNewLine Msg = Msg & "Processing the monthly report will take approximately " Msg = Msg & "15 minutes. It will generate a 30-page report for all " Msg = Msg & "sales offices for the current month." Title = "XYZ Marketing Company" Config = vbYesNo + vbQuestion Ans = MsgBox(Msg, Config, Title) If Ans = vbYes Then RunReport If Ans = vbNo Then Exit Sub End Sub
This example demonstrates an efficient way to specify a longer message in a message box. A variable (Msg
) and the concatenation operator (&
) are used to build the message in a series of statements. vbNewLine
is a constant that represents a break character. (Using two line breaks inserts a blank line.) The title argument is also used to display a different title in the message box. Figure 41.5 shows how this message box appears when the procedure is executed.
The InputBox
and MsgBox
functions do just fine for many cases, but if you need to obtain more information, you need to create a UserForm.
Following is a list of the general steps that you typically take to create a UserForm:
Determine exactly how the dialog box is going to be used and where it is to fit into your VBA macro.
Activate the VB Editor and insert a new UserForm.
Add the appropriate controls to the UserForm.
Create a VBA macro to display the UserForm. This macro goes in a normal VBA module.
Create event handler VBA procedures that are executed when the user manipulates the controls (for example, clicks the OK button). These procedures go in the code module for the UserForm.
The following sections provide more details on creating a UserForm.
To create a dialog box, you must first insert a new UserForm in the VB Editor window. To activate the VB Editor, choose Developer Visual Basic (or press Alt+F11). Make sure that the correct workbook is selected in the Project window and then choose Insert UserForm. The VB Editor displays an empty UserForm, shown in Figure 41.6. When you activate a UserForm, the VB editor displays the Toolbox, which is used to add controls to the UserForm.
The Toolbox, also shown in Figure 41.6, contains various ActiveX controls that you can add to your UserForm.
When you move the mouse pointer over a control in the Toolbox, the control’s name appears. To add a control, click and drag it in the form. After adding a control, you can move it or change its size.
Table 41.2 lists the Toolbox controls.
Table 41.2. Toolbox Controls
Control | Description |
---|---|
| Lets you select other controls by dragging. |
| Adds a label (a container for text). |
| Adds a text box (allows the user to type text). |
| Adds a combo box (a drop-down list). |
| Adds a list box (to allow the user to select an item from a list). |
| Adds a check box (to control Boolean options). |
| Adds an option button (to allow a user to select from multiple options). |
| Adds a toggle button (to control Boolean options). |
| Adds a frame (a container for other objects). |
| Adds a command button (a clickable button). |
| Adds a tab strip (a container for other objects). |
| Adds a multipage control (a container for other objects). |
| Adds a scroll bar (to specify a value by dragging a bar). |
| Adds a spin button (to specify a value by clicking up or down). |
| Adds a control that can contain an image. |
| Adds a reference edit control (lets the user select a range). |
You can also place some of these controls directly on your worksheet. Refer to Chapter 42 for details.
Every control that you add to a UserForm has several properties that determine how the control looks and behaves. You can change some of these properties (such as Height
and Width
) by clicking and dragging the control’s border. To change other properties, use the Properties window.
To display the Properties window, choose View Properties Window (or press F4). The Properties window displays a list of properties for the selected control. (Each control has a different set of properties.) If you click the UserForm itself, the Properties window displays properties for the form. Figure 41.7 shows the Properties window for a CommandButton
control.
To change a property, select the property in the Property window and then enter a new value. Some properties (such as BackColor
) enable you to select a property from a list. The top of the Properties window contains a drop-down list that enables you to select a control to work with. You can also click a control to select it and display its properties.
When you set properties by using the Property window, you’re setting properties at design time. You can also use VBA to change the properties of controls while the UserForm is displayed (that is, at run time).
A complete discussion of all the properties is well beyond the scope of this book—and it would indeed be very dull reading. To find out about a particular property, select it in the Property window and press F1. The Help for UserForm controls is extremely thorough.
When you insert a UserForm, that form can also hold VBA Sub procedures to handle the events that are generated by the UserForm. An event is something that occurs when the user manipulates a control. For example, clicking a button causes an event. Selecting an item in a list box control also triggers an event. To make a UserForm useful, you must write VBA code to do something when an event occurs.
Event-handler procedures have names that combine the control with the event. The general form is the control’s name, followed by an underscore, and then the event name. For example, the procedure that is executed when the user clicks a button named MyButton is MyButton_Click
.
You also need to write a procedure to display the UserForm. You use the Show
method of the UserForm
object. The following procedure displays the UserForm named UserForm1
:
Sub ShowDialog() UserForm1.Show End Sub
This procedure should be stored in a regular VBA module (not the code module for the UserForm). If your VB project doesn’t have a normal VBA module, choose Insert Module to add one.
When the ShowDialog
procedure is executed, the UserForm is displayed. What happens next depends on the event-handler procedures that you create.
The preceding section is, admittedly, rudimentary. This section demonstrates how to develop a UserForm. This example is rather simple. The UserForm displays a message to the user—something that can be accomplished more easily by using the MsgBox
function. However, a UserForm gives you a lot more flexibility in terms of formatting and layout of the message.
This workbook is available on the companion CD-ROM. The file is named show message.xlsm.
If you’re following along on your computer, start with a new workbook. Then follow these steps:
Choose Developer Visual Basic (or press Alt+F11) to activate the VB Editor window.
In the VB Editor Project window, double-click your workbook’s name to activate it.
Choose Insert UserForm. The VB Editor adds an empty form named UserForm1
and displays the Toolbox.
Press F4 to display the Properties window and then change the following properties of the UserForm
object:
Property | Change To |
| AboutBox |
| About This Workbook |
Use the Toolbox to add a Label
object to the UserForm
.
Select the Label
object. In the Properties window, enter any text that you want for the label’s Caption
.
In the Properties window, click the Font
property and adjust the font. You can change the typeface, size, and so on. The changes then appear in the form. Figure 41.8 shows an example of a formatted Label
control.
Add a CommandButton
object to the UserForm and change the following properties for the CommandButton:
Property | Change To |
| OKButton |
| OK |
| True |
Make other adjustments so that the form looks good to you. You can change the size of the form or move or resize the controls.
At this point, the UserForm has all the necessary controls. But what’s missing is a way to display the UserForm. While you’re developing the UserForm, you can press F5 to display it and see how it looks.
This section explains how to write a VBA Sub procedure to display the UserForm when Excel is active.
Insert a VBA module by choosing Insert Module.
In the empty module, enter the following code:
Sub ShowAboutBox() AboutBox.Show End Sub
Activate Excel (Alt+F11 is one way).
Choose Developer Code Macros to display the Macros dialog box. Or you can press Alt+F8 or click the Play Macro button in the status bar.
In the Macros dialog box, select ShowAboutBox from the list of macros and click Run. The UserForm then appears.
If you click the OK button, notice that it doesn’t close the UserForm as you may expect. This button needs to have an event-handler procedure in order for it to do anything when it’s clicked. To dismiss the UserForm, click the close button in its title box.
You may prefer to display the UserForm by clicking a CommandButton on your worksheet. See Chapter 42 for details on attaching a macro to a Worksheet CommandButton.
An event-handler procedure is executed when an event occurs. In this case, you need a procedure to handle the Click
event that’s generated when the user clicks the OK button.
Activate the VB Editor. (Pressing Alt+F11 is the fastest way.)
Activate the AboutBox UserForm by double-clicking its name in the Project window.
Double-click the CommandButton
control. The VB Editor activates the code module for the UserForm and inserts some boilerplate code, as shown in Figure 41.9.
Insert the following statement before the End Sub
statement:
Unload AboutBox
This statement simply dismisses the UserForm. The complete event-handler procedure is
Private Sub OKButton_Click() Unload AboutBox End Sub
The example in this section is an enhanced version of the ChangeCase
procedure presented at the beginning of the chapter. Recall that the original version of this macro changes the text in the selected cells to uppercase characters. This modified version asks the user what type of case change to make: uppercase, lowercase, or proper case (initial capitals).
This UserForm needs one piece of information from the user: the type of change to make to the text. Because only one option can be selected, OptionButton
controls are appropriate. Start with an empty workbook and follow these steps to create the UserForm:
Press Alt+F11 to activate the VB Editor window.
In the VB Editor, choose Insert UserForm. The VB Editor adds an empty form named UserForm1
and displays the Toolbox.
Press F4 to display the Properties window and then change the following property of the UserForm
object:
Property | Change To |
| Case Changer |
Add a CommandButton
object to the UserForm and then change the following properties for the CommandButton:
Property | Change To |
| OKButton |
| OK |
| True |
Add another CommandButton
object and then change the following properties:
Property | Change To |
| CancelButton |
| Cancel |
| True |
Add an OptionButton
control and then change the following properties. (This option is the default, so its Value
property should be set to True.
)
Property | Change To |
| OptionUpper |
| Upper Case |
| True |
Add a second OptionButton
control and then change the following properties:
Property | Change To |
| OptionLower |
| Lower Case |
Add a third OptionButton
control and then change the following properties:
Property | Change To |
| OptionProper |
| Proper Case |
Adjust the size and position of the controls and the form until your UserForm resembles the UserForm shown in Figure 41.10. Make sure that the controls do not overlap.
At this point, the UserForm has all the necessary controls. What’s missing is a way to display the form. This section explains how to write a VBA procedure to display the UserForm.
Make sure that the VB Editor window is activated.
Insert a module by choosing Insert Module.
In the empty module, enter the following code:
Sub ShowUserForm() UserForm1.Show End Sub
Choose Run Sub/UserForm (or press F5).
The Excel window is then activated, and the new UserForm is displayed, as shown in Figure 41.11. The OptionButton
controls work, but clicking the OK and Cancel buttons has no effect. These two buttons need to have event-handler procedures. Click the Close button in the title bar to dismiss the UserForm.
This section explains how to create two event-handler procedures: one to handle the Click
event for the CancelButton CommandButton
and the other to handle the Click
event for the OKButton CommandButton
. Event handlers for the OptionButton
controls are not necessary. The VBA code can determine which of the three OptionButton
controls is selected.
Event-handler procedures are stored in the UserForm code module. To create the procedure to handle the Click
event for the CancelButton, follow these steps:
Activate the UserForm1 form by double-clicking its name in the Project window.
Double-click the CancelButton
control. The VB Editor activates the code module for the UserForm and inserts an empty procedure.
Insert the following statement before the End Sub
statement:
Unload UserForm1
That’s all there is to it. The following is a listing of the entire procedure that’s attached to the Click
event for the CancelButton:
Private Sub CancelButton_Click() Unload UserForm1 End Sub
This procedure is executed when the CancelButton is clicked. It consists of a single statement that unloads the UserForm1 form.
The next step is to add the code to handle the Click
event for the OKButton control. Follow these steps:
Select OKButton from the drop-down list at the top of the module. The VB Editor begins a new procedure called OKButton_Click
.
Enter the following code. (The first and last statements have already been entered for you by the VB Editor.)
Private Sub OKButton_Click() Application.ScreenUpdating = False ' Exit if a range is not selected If TypeName(Selection) <> "Range" Then Exit Sub ' Upper case If OptionUpper Then For Each cell In Selection If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbUpperCase) End If Next cell End If ' Lower case If OptionLower Then For Each cell In Selection If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, vbLowerCase) End If Next cell End If ' Proper case If OptionProper Then For Each cell In Selection If Not cell.HasFormula Then cell.Value = StrConv(cell.Value, bProperCase) End If Next cell End If Unload UserForm1 End Sub
The macro starts by turning off screen updating, which makes the macro run a bit faster. Next, the code checks the type of the selection. If a range is not selected, the procedure ends. The remainder of the procedure consists of three separate blocks. Only one block is executed, determined by which OptionButton
is selected. The selected OptionButton
has a value of True
. Finally, the UserForm is unloaded (dismissed).
To try out the UserForm from Excel, follow these steps:
Activate Excel.
Enter some text into some cells.
Select the range with the text.
Choose Developer Code Macros (or press Alt+F8).
In the Macros dialog box, select ShowUserForm from the list of macros and then click OK. The UserForm appears.
Make your choice and click OK.
Try it with a few more selections. Notice that if you click Cancel, the UserForm is dismissed, and no changes are made.
At this point, everything should be working properly. However, you have no quick and easy way to execute the macro. A good way to execute this macro would be from a button on the worksheet. You can use the following steps:
Choose Developer Controls Insert and click the Button control in the Form Controls group.
Click and drag in the worksheet to create the button.
Excel display the Assign Macro dialog box.
In the Assign Macro dialog box, select ShowUserForm and click OK.
At this point, the button is still selected, so you can change the text to make it more descriptive. You can also right-click the button at any time to change the text.
After performing the preceding steps, clicking the button executes the macro and displays the UserForm.
The button in this example is from the Form Controls group. Excel also provides a button in the ActiveX Controls group. See Chapter 42 for more information about the ActiveX Controls group.
You can also add a button to your Quick Access Toolbar (QAT) that, when clicked, executes your macro. Unfortunately, the button works only when the workbook that contains the macro is active. To add a macro button to your QAT, use the following steps:
Make sure that the workbook containing the macro is open.
Right-click anywhere in the Ribbon and then select Customize Quick Access Toolbar. The Excel Options dialog box appears, with the Customization setting selected.
Choose Macros from the drop-down menu on the left (labeled Choose Commands From). You’ll see your macro listed.
Select the macro’s name and click Add to adds the item to the list on the right.
If you’d like to change the icon, click Modify and choose a new image. You can also change the Display Name.
Click OK to close the Excel Options dialog box. After performing these steps, your QAT will have a new icon.
Creating UserForms can make your macros much more versatile. You can create custom commands that display dialog boxes that look exactly like those that Excel uses. This section contains some additional information to help you develop custom dialog boxes that work like those that are built into Excel.
Custom dialog boxes should not discriminate against those who prefer to use the keyboard rather than a mouse. All of Excel’s dialog boxes work equally well with a mouse and a keyboard because each control has an associated accelerator key. The user can press Alt plus the accelerator key to work with a specific dialog box control.
Adding accelerator keys to your UserForms is a good idea. You do this in the Properties window by entering a character for the Accelerator
property.
The letter that you enter as the accelerator key must be a letter that is contained in the caption of the object. It can be any letter in the text (not necessarily the first letter). You should make sure that an accelerator key is not duplicated in a UserForm. If you have duplicate accelerator keys, the accelerator key acts on the first control in the tab order of the UserForm. Then, pressing the accelerator key again takes you to the next control.
Some controls (such as edit boxes) don’t have a caption property. You can assign an accelerator key to a label that describes the control. Pressing the accelerator key then activates the next control in the tab order (which you should ensure is the edit box).
The previous section refers to a UserForm’s tab order. When you’re working with a UserForm, pressing Tab and Shift+Tab cycles through the dialog box’s controls. When you create a UserForm, you should make sure that the tab order is correct. Usually, it means that tabbing should move through the controls in a logical sequence.
To view or change the tab order in a UserForm, choose View Tab Order to display the Tab Order dialog box. You can then select a control from the list; use the Move Up and Move Down buttons to change the tab order for the selected control.
Mastering UserForms takes practice. You should closely examine the dialog boxes that Excel uses to get a feeling for how dialog boxes are designed. You can duplicate many of the dialog boxes that Excel uses.
The best way to learn more about creating dialog boxes is by using the VBA Help system.