Chapter 16
In This Chapter
Finding out when to use UserForms
Understanding UserForm objects
Displaying a UserForm
Creating a UserForm that works with a useful macro
A UserForm is useful if your VBA macro needs to pause and get some information from a user. For example, your macro may have some options that can be specified in a UserForm. If only a few pieces of information are required (for example, a Yes/No answer or a text string), one of the techniques I describe in Chapter 15 may do the job. But if you need to obtain more information, you must create a UserForm. In this chapter, I introduce you to UserForms. You'll be pleased to make their acquaintance.
This section describes a situation in which a UserForm is useful. The following macro changes the text in each cell in the selection to uppercase letters. It does this by using the VBA built-in UCase function.
Sub ChangeCase()
Dim WorkRange As Range, cell As Range
' Exit if a range is not selected
If TypeName(Selection) <> "Range" Then Exit Sub
' Process only text cells, no formulas
On Error Resume Next
Set WorkRange = Selection.SpecialCells _
(xlCellTypeConstants, xlCellTypeConstants)
For Each cell In WorkRange
cell.Value = UCase(cell.Value)
Next cell
End Sub
You can make this macro even more useful. For example, it would be nice if the macro could also change the text in the cells to either lowercase or proper case (capitalizing the first letter in each word). One approach is to create two additional macros — one for lowercase and one for proper case. Another approach is to modify the macro to handle the other options. If you use the second approach, you need some method of asking the user which type of change to make to the cells.
The solution is to display a dialog box like the one shown in Figure 16-1. You create this dialog box on a UserForm in the VBE and display it by using a VBA macro. In the next section, I provide step-by-step instructions for creating this dialog box.
When creating a UserForm, you usually take the following general steps:
Press Alt+F11 to activate the VBE and insert a new UserForm object.
A UserForm object holds a single UserForm.
Add controls to the UserForm.
Controls include items such as text boxes, buttons, check boxes, and list boxes.
Write event-handler procedures for the controls (for example, a macro that executes when the user clicks a button in the dialog box).
These procedures are stored in the Code window for the UserForm object.
Don't worry if some of these steps seem foreign. I provide more details in the following sections, along with step-by-step instructions for creating a UserForm.
When you are designing a UserForm, you are creating what developers call the Graphical User Interface (GUI) to your application. GUI also stands for Gaming Under the Influence, but that's not relevant here.
Take some time to consider what your form should look like and how your users are likely to want to interact with the elements on the UserForm. Try to guide them through the steps they need to take on the form by carefully considering the arrangement and wording of the controls. Like most things VBA-related, the more you do it, the easier it gets.
Each dialog box that you create is stored in its own UserForm object — one dialog box per UserForm. You create and access these UserForms in the Visual Basic Editor.
Insert a UserForm object by following these steps:
Choose Insert ⇒ UserForm.
The VBE inserts a new UserForm object, which contains an empty dialog box.
Figure 16-2 shows a UserForm — an empty dialog box. Your job, if you choose to accept it, is to add some controls to this UserForm.
When you activate a UserForm, the VBE displays the Toolbox in a floating window (refer to Figure 16-2). You use the tools in the Toolbox to add controls to your UserForm. If, for some reason, the Toolbox doesn't appear when you activate your UserForm, choose View ⇒ Toolbox.
To add a control, just click the desired control in the Toolbox and drag it into the dialog box to create the control. After you add a control, you can move and resize it by using standard techniques.
Table 16-1 lists the various tools, as well as their capabilities. To determine which tool is which, hover your mouse pointer over the control and read the small pop-up description.
Table 16-1 Toolbox Controls
Control |
What It Does |
Label |
Shows text |
TextBox |
Allows the user to enter text |
ComboBox |
Displays a drop-down list |
ListBox |
Displays a list of items |
CheckBox |
Provides options such as on/off or yes/no |
OptionButton |
Allows the user to select one of several options; used in groups of two or more |
ToggleButton |
Enables the user to switch a button on or off |
Frame |
Contains other controls |
CommandButton |
A clickable button |
TabStrip |
Displays tabs |
MultiPage |
A tabbed container for other objects |
ScrollBar |
Enables the user to drag a bar to establish a setting |
SpinButton |
Enables the user to click a button to change a value |
Image |
Holds an image |
RefEdit |
Allows the user to select a range |
Every control you add to a UserForm has properties that determine how the control looks or behaves. In addition, the UserForm itself has its own set of properties. You can change these properties with the aptly named Properties window. Figure 16-3 shows the Properties window when a CommandButton control is selected.
The Properties window appears when you press F4, and the properties shown in this window depend on what is selected. If you select a different control, the properties change to those appropriate for that control. To hide the Properties window and get it out of the way, click the Close button in its title bar. Pressing F4 always brings it back when you need it.
Properties for controls include the following:
Each control has its own set of properties (although many controls have some common properties). To change a property using the Properties window, follow these steps:
If you select the UserForm itself (not a control on the UserForm), you can use the Properties window to adjust UserForm properties.
Chapter 17 tells you everything you need to know about working with dialog box controls.
Every UserForm object has a Code module that holds the VBA code (the event-handler procedures) that is executed when the user works with the dialog box. To view the Code module, press F7. The Code window is empty until you add some procedures. Press Shift+F7 to return to the dialog box.
Here's another way to switch between the Code window and the UserForm display: Use the View Code and View Object buttons in the Project window’s title bar. Or right-click the UserForm and choose View Code. If you're viewing code, double-click the UserForm name in the Project window to return to the UserForm.
You display a UserForm by using the UserForm’s Show method in a VBA procedure.
The following procedure displays the dialog box named UserForm1:
Sub ShowDialogBox()
UserForm1.Show
' Other statements can go here
End Sub
When Excel displays the dialog box, the ShowDialogBox macro halts until the user closes the dialog box. Then VBA executes any remaining statements in the procedure. Most of the time, you won’t have any more code in the procedure. As you later see, you put your event-handler procedures in the Code window for the UserForm. These procedures kick in when the user works with the controls on the UserForm.
The VBE provides a name for each control you add to a UserForm. The control’s name corresponds to its Name property. Use this name to refer to a particular control in your code. For example, if you add a CheckBox control to a UserForm named UserForm1, the CheckBox control is named CheckBox1 by default. You can use the Properties box to make this control appear with a check mark. Or you can write code to do it:
UserForm1.CheckBox1.Value = True
Most of the time, you write the code for a UserForm in the UserForm’s code module. If that's the case, you can omit the UserForm object qualifier and write the statement like this:
CheckBox1.Value = True
Your VBA code can also check various properties of the controls and take appropriate actions. The following statement executes a macro named PrintReport if the check box (named CheckBox1) is checked:
If CheckBox1.Value = True Then Call PrintReport
I discuss this topic in detail in Chapter 17.
This section’s UserForm example is an enhanced version of the ChangeCase macro from the beginning of the chapter. The original version of this macro changes the text in the selected cells to uppercase. This modified version uses a UserForm to ask the user which type of change to make: uppercase, lowercase, or proper case.
This dialog box needs to obtain one piece of information from the user: the type of change to make to the text. Because the user has three choices, your best bet is a dialog box with three OptionButton controls. The dialog box also needs two more buttons: an OK button and a Cancel button. Clicking the OK button runs the code that does the work. Clicking the Cancel button causes the macro to finish without doing anything.
These steps create the UserForm. Start with an empty workbook, and follow these steps:
Choose Insert ⇒ UserForm.
The VBE inserts a new UserForm object with an empty dialog box.
(Optional) The dialog box is a bit too large, so you may want to click it and use the handles (on the right and bottom sides) to make it smaller.
You can do Step 6 after you position all the controls in the dialog box.
Ready to add two CommandButtons — OK and Cancel — to the dialog box? Follow along:
In the Toolbox, drag a CommandButton into the dialog box to create a button.
As you see in the Properties box, the button has a default name and caption: CommandButton1.
Property |
Change To |
Name |
OKButton |
Caption |
OK |
Default |
True |
Property |
Change To |
Name |
CancelButton |
Caption |
Cancel |
Cancel |
True |
In this section, you add three OptionButtons to the dialog box. Before adding the OptionButtons, you add a Frame object that contains the OptionButtons. The Frame isn’t necessary, but it makes the dialog box look a bit more professional so users won’t think it was designed by an amateur.
To add the OptionButtons, follow these steps:
In the Toolbox, click the Frame tool and drag it into the dialog box.
This step creates a frame to hold the options buttons.
In the Toolbox, click the OptionButton tool and drag it into the dialog box (within the Frame).
Doing this creates an OptionButton control.
Property |
Change To |
Name |
OptionUpper |
Caption |
Upper Case |
Accelerator |
U |
Value |
True |
Setting the Value property to True makes this OptionButton the default.
Property |
Change To |
Name |
OptionLower |
Caption |
Lower Case |
Accelerator |
L |
Property |
Change To |
Name |
OptionProper |
Caption |
Proper Case |
Accelerator |
P |
Adjust the size and position of the OptionButtons, Frame, and dialog box.
Your UserForm should look something like Figure 16-5.
If you'd like a sneak preview to see what the UserForm looks like when it's displayed, press F5. None of the controls is working yet, so you need to click the red X in the title bar to close the dialog box.
The Accelerator property determines which letter in the caption is underlined — and more important, it determines what Alt+key combination selects that control. For example, you can select the Lower Case option by pressing Alt+L because the L is underlined. Accelerator keys are optional, but some users prefer to use the keyboard to navigate dialog boxes.
You may wonder why the OptionButtons have accelerator keys but the CommandButtons lack such a feature. Generally, OK and Cancel buttons never have accelerator keys because they can be accessed from the keyboard. Pressing Enter is equivalent to clicking OK because the control’s Default property is True. Pressing Esc is equivalent to clicking Cancel because the control’s Cancel property is True.
Now it's time to make the UserForm actually do something. Here’s how to add an event-handler procedure for the Cancel and OK buttons:
Double-click the Cancel button.
VBE activates the Code window for the UserForm and inserts an empty procedure:
Private Sub CancelButton_Click()
End Sub
The procedure named CancelButton_Click is executed when the Cancel button is clicked, but only when the dialog box is displayed. In other words, clicking the Cancel button when you’re designing the dialog box won’t execute the procedure. Because the Cancel button’s Cancel property is set to True, pressing Esc also triggers the CancelButton_Click procedure.
Unload UserForm1
This statement closes the UserForm when the Cancel button is clicked.
Double-click the OK button.
VBE activates the code window for the UserForm and inserts an empty Sub procedure called OKButton_Click.
When the UserForm is displayed, clicking OK executes this procedure. Because this button has its Default property set to True, pressing Enter also executes the OKButton_Click procedure.
Private Sub OKButton_Click()
Dim WorkRange As Range
Dim cell As Range
' Process only text cells, no formulas
On Error Resume Next
Set WorkRange = Selection.SpecialCells _
(xlCellTypeConstants, xlCellTypeConstants)
' Upper case
If OptionUpper Then
For Each cell In WorkRange
cell.Value = UCase(cell.Value)
Next cell
End If
' Lower case
If OptionLower Then
For Each cell In WorkRange
cell.Value = LCase(cell.Value)
Next cell
End If
' Proper case
If OptionProper Then
For Each cell In WorkRange
cell.Value = Application. _
WorksheetFunction.Proper(cell.Value)
Next cell
End If
Unload UserForm1
End Sub
The preceding code is an enhanced version of the original ChangeCase macro that I present at the beginning of the chapter. The macro consists of three separate blocks of code. This code uses three If-Then structures; each one has a For Each-Next loop. Only one block is executed, determined by which OptionButton the user selects. The last statement unloads (closes) the dialog box after the work is finished.
Here's something kind of odd. Notice that VBA has a UCase function and an LCase function, but it doesn't have a function to convert text to proper case. Therefore, I use Excel’s PROPER worksheet function (preceded by Application.WorksheetFunction) to do the proper case conversion.
Another option is to use the VBA StrConv function, with a second argument of vbProperCase. (See the Help system for details.) The StrConv function is not available in all Excel versions, so I use the PROPER worksheet function instead. It's worth noting that the StrConv beats the PROPER function at converting to proper case. Excel's PROPER function always capitalizes the letter that follows an apostrophe. So the word can't becomes Can'T. StrConv doesn't do that.
You're almost finished with this project. The only thing missing is a way to display the dialog box. Follow these steps to create the procedure that makes the dialog box appear:
In the VBE window, choose Insert ⇒ Module.
The VBE adds an empty VBA module (named Module1) to the project.
Sub ChangeCase()
If TypeName(Selection) = "Range" Then
UserForm1.Show
Else
MsgBox "Select a range.", vbCritical
End If
End Sub
This procedure is pretty simple. It checks to make sure that a range is selected. If so, the dialog box is displayed (using the Show method). The user then interacts with the dialog box, and the code stored in the UserForm’s Code pane is executed. If a range is not selected, the user sees a MsgBox with the text Select a range.
At this point, everything should be working properly. But you still need an easy way to execute the macro. Assign a shortcut key (Ctrl+Shift+C) that executes the ChangeCase macro by following these steps:
Click the Options button.
Excel displays its Macro Options dialog box.
After you perform this operation, pressing Ctrl+Shift+C executes the ChangeCase macro, which displays the UserForm if a range is selected.
You can also make this macro available from the Quick Access toolbar. Right-click the Quick Access toolbar and choose Customize Quick Access Toolbar. The Excel Options dialog box appears, and you'll find the ChangeCase macro listed below Macros (see Figure 16-7).
Finally, you need to test the macro and dialog box to make sure they work properly, as follows:
Select some cells that contain text.
You can even select entire rows or columns.
Press Ctrl+Shift+C.
The UserForm appears. Figure 16-8 shows how it should look.
Make your choice, and click OK.
If you did everything correctly, the macro makes the specified change to the text in the selected cells.
If you test this procedure when only one cell is selected, you'll find that all the cells on the worksheet are processed. That behavior is a byproduct of using the SpecialCells method. If you prefer to be able to process just one cell, change the first block of code to this:
If Selection.Count = 1 Then
Set WorkRange = Selection
Else
Set WorkRange = Selection.SpecialCells _
(xlCellTypeConstants, xlCellTypeConstants)
End If
Figure 16-9 shows the worksheet after converting the text to uppercase. Notice that the formula in cell B15 and the date in cell B16 were not changed. The macro works only with cells that contain text.
As long as the workbook is open, you can execute the macro from any other workbook. If you close the workbook that contains your macro, Ctrl+Shift+C no longer has any function.
If the macro doesn’t work properly, double-check the preceding steps to locate and correct the error. Don’t be alarmed; debugging is a normal part of developing macros. As a last resort, download the completed workbook from this book’s website and try to figure out where you went wrong.