Chapter 41. Creating UserForms

IN THIS CHAPTER

Why create UserForms

UserForm alternatives

Creating UserForms: An overview

UserForm examples

More on creating UserForms

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 in to Excel. These dialog boxes are known as UserForms.

41.1. Why Create 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 (VB) Editor, and it is displayed by a VBA macro.

Figure 41.1. A UserForm that asks the user to select an option.

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.

41.2. UserForm Alternatives

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

41.2.1. The InputBox function

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: (Required) Text that is displayed in the input box

• title: (Optional) Text that appears in the input box's title bar

• default: (Optional) The default value

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.

Figure 41.2. This dialog box is displayed by the VBA InputBox function.

41.2.2. The MsgBox function

The VBA MsgBox function is a handy way to display information and to solicit simple input from users. I use the VBA 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: (Required) Text that is displayed in the message box

• buttons: (Optional) The code for the buttons that are to appear in the message box

• title: (Optional) Text that appears in the message box's title bar

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.

Figure 41.3. A simple message box, displayed with the VBA MsgBox function.

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 Table 41.1) 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 buttons argument. Table 41.1 lists the built-in constants that you can use for the buttons argument. You can specify which buttons to display, whether an icon appears, and which button is the default.

The following example uses a combination of constants to display a message box with a Yes button and a No button (vbYesNo), and a question mark icon (vbQuestion). The second button (the No 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 the routine executes the procedure named RunReport (which is not shown). If the user clicks the No button (or presses Enter), the procedure is ended with no action. Because the title argument was omitted in the MsgBox function, Excel uses the default title (Microsoft Excel).

Figure 41.4. The second argument of the MsgBox function determines what appears in the message box.

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. The Config variable stores the constants that generate Yes and No buttons and a question mark icon. Figure 41.5 shows how this message box appears when the procedure is executed.

Figure 41.5. A message box with a longer message and a title.

41.3. Creating UserForms: An Overview

The InputBox and MsgBox functions are adequate for many situations, but if you need to obtain more information, you need to create a UserForm.

The following is a list of the general steps that you typically take to create a UserForm:

The following sections provide more details on creating a UserForm.

41.3.1. Working with UserForms

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.

Figure 41.6. An empty UserForm.

41.3.2. Adding controls

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 into the form. After adding a control, you can move it or change its size.

Table 41.2 lists the Toolbox controls.

41.3.3. Changing the properties of a control

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

Figure 41.7. The Properties window for a CommandButton control.

When you set properties via the Properties 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 Properties window and press F1. The Help for UserForm controls is extremely thorough.

41.3.4. Handling events

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.

41.3.5. Displaying a UserForm

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 regular VBA module, choose Insert Module to add one.

When the ShowDialog procedure is executed, the UserForm is displayed. What happens next depends upon the event-handler procedures that you create.

41.4. A UserForm Example

The preceding section is, admittedly, rudimentary. This section demonstrates, in detail, 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 more flexibility in terms of formatting and layout of the message.

41.4.1. Creating the UserForm

If you're following along on your computer, start with a new workbook. Then follow these steps:

41.4.2. Testing the UserForm

At this point, the UserForm has all the necessary controls. 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. To close the UserForm, click the X button in the title bar.

This section explains how to write a VBA Sub procedure to display the UserForm when Excel is active.

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 (X) in its title bar.

41.4.3. Creating an event-handler procedure

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.

This statement simply dismisses the UserForm by using the Unload statement. The complete event-handler procedure is

Private Sub OKButton_Click()
   Unload AboutBox
End Sub

41.5. Another UserForm Example

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

41.5.1. Creating the UserForm

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:

Figure 41.10. The UserForm after adding controls and adjusting some properties.

The VB Editor provides several useful commands to help you size and align the controls. For example, you can make a group of selected controls the same size, or move them so they are all aligned to the left. Select the controls that you want to work with and then choose a command from the Format menu. These commands are fairly self-explanatory, and the Help system has complete details.

41.5.2. Testing the UserForm

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.

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.

Figure 41.11. Displaying the UserForm.

41.5.3. Creating event-handler procedures

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:

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:

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

41.5.4. Testing the UserForm

To try out the UserForm from Excel, follow these steps:

Try it with a few more selections. Notice that if you click Cancel, the UserForm is dismissed, and no changes are made.

41.5.5. Making the macro available from a worksheet button

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:

After performing the preceding steps, clicking the button executes the macro and displays the UserForm.

41.5.6. Making the macro available on your Quick Access toolbar

If you would like to use this macro while other workbooks are active, you may want to add a button to your Quick Access toolbar. Use the following steps:

41.6. More on Creating UserForms

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 in to Excel.

41.6.1. Adding accelerator keys

Custom dialog boxes should not discriminate against those who prefer to use the keyboard rather than a mouse. All Excel 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. However, 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).

41.6.2. Controlling tab order

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.

41.7. Learning More

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. Pressing F1 is the quickest way to display the Help window.