Microsoft Excel 2016 BIBLE (2016)

IN THIS CHAPTER

1. Understanding why you may want to create UserForms

2. Identifying UserForm alternatives

3. Creating UserForms: An overview

4. Looking at some UserForm examples

5. Finding more information 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.

Why Create UserForms?

Some macros that you create behave 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 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's 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.

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.

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.

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

Table 41.1 Constants Used in the MsgBox Function

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

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:

1. Determine exactly how the dialog box is going to be used and where it will fit into your VBA macro.

2. Activate the VB Editor and insert a new UserForm.

3. Add the appropriate controls to the UserForm.

4. Create a VBA macro to display the UserForm. This macro goes in a normal VBA module.

5. 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 offer more details on creating a UserForm.

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

Adding controls

The Toolbox, also shown in Figure 41.6, contains various ActiveX controls that you can add to your UserForm. If the Toolbox is not visible, choose View Toolbox.

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.

Table 41.2 Toolbox Controls

You can also place some of these controls directly on your worksheet. See Chapter 42, “Using UserForm Controls in a Worksheet,” for details.

Changing the properties of a control

Every control that you add to a UserForm has several properties that determine the way 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.

Figure 41.7 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.

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 dull reading. To find out about a particular property, select it in the Properties window and press F1 for help.

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

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 on the event handler procedures that you create.

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 MsgBoxfunction. However, a UserForm gives you more flexibility in terms of formatting and layout of the message.

This workbook is available on this book's website at www.wiley.com/go/excel2016bible. The file is named show message.xlsm.

Creating the UserForm

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

1. Choose Developer Code Visual Basic (or press Alt+F11). The VB Editor window appears.

2. Double-click your workbook's name to activate it.

3. Choose Insert UserForm. The VB Editor adds an empty form named UserForm1 and displays the Toolbox.

4. Press F4 to display the Properties window, and then change the following properties of theUserFormobject:

5. Use the Toolbox to add a Label object to the UserForm. If the Toolbox is not visible, choose View Toolbox.

6. Select theLabelobject, and in the Properties window, enter any text that you want for the label'sCaption.

7. In the Properties window, click theFontproperty 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. In this example, the TextAlign property was set to the code that center aligns the text.

2 - fmTextAlignCenter

Figure 41.8 A Label control, after changing its Font properties.

8. Use the Toolbox and add aCommandButtonobject to the UserForm; then use the Properties window to change the following properties for theCommandButton:

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

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 Close button (X) in the title bar.

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

1. Insert a VBA module by choosing Insert Module.

2. In the empty module, enter the following code:

3. Sub ShowAboutBox()

4. AboutBox.Show

End Sub

5. Press Alt+F11 to activate Excel.

6. Choose Developer Code Macros (or press Alt+F8). The Macros dialog box appears.

7. Select ShowAboutBox from the list of macros, and then click Run. The UserForm 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 for it to do anything when it's clicked. To dismiss the UserForm, click the Close button (X) in its title bar.

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.

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.

1. Press Alt+F11 to activate the VB Editor.

2. Activate theAboutBoxUserForm by double-clicking its name in the Project window.

3. Double-click theCommandButtoncontrol. The VB Editor activates the code module for the UserForm and inserts some boilerplate code, as shown in Figure 41.9.

Figure 41.9 The code module for the UserForm.

4. Insert the following statement before theEnd Substatement:

Unload AboutBox

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

After you add the event procedure, clicking the OK button works as it should.

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

This workbook is available on this book's website at www.wiley.com/go/excel2016bible. The file is change case.xlsm.

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:

1. Press Alt+F11 to activate the VB Editor window.

2. In the VB Editor, choose Insert &cmdarr; UserForm. The VB Editor adds an empty form named UserForm1 and displays the Toolbox.

3. Press F4 to display the Properties window, and then change the following property of theUserFormobject:

4. Add aCommandButtonobject to the UserForm and then change the following properties for theCommandButton:

5. Add anotherCommandButtonobject and then change the following properties:

6. Add anOptionButtoncontrol and then change the following properties. (This option is the default, so its Value property should be set to True.)

7. Add a secondOptionButtoncontrol and then change the following properties:

8. Add a thirdOptionButtoncontrol and then change the following properties:

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

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

Tip

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're 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.

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:

1. Make sure that the VB Editor window is activated.

2. Insert a module by choosing Insert Module.

3. In the empty module, enter the following code:

4. Sub ShowUserForm()

5. UserForm1.Show

End Sub

6. Choose Run Sub/UserForm (or press F5). The Excel window is 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.

Figure 41.11 Displaying the UserForm.

7. Click the Close button (X) in the title bar to dismiss the UserForm.

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:

1. Activate the UserForm1 form by double-clicking its name in the Project window.

2. Double-click theCancelButtoncontrol. The VB Editor activates the code module for the UserForm and inserts an empty procedure.

3. Insert the following statement before theEnd Substatement:

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:

1. SelectOKButtonfrom the drop-down list at the top of the module or reactivate the UserForm and double-click theOKButtoncontrol. The VB Editor begins a new procedure called OKButton_Click.

2. Enter the following code. The VB Editor has already entered the first and last statements for you:

Private Sub OKButton_Click()

' 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 checking the type of 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).

Testing the UserForm

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

1. Activate Excel.

2. Enter some text into a range of cells.

3. Select the range with the text.

4. Choose Developer Code Macros (or press Alt+F8). The Macros dialog box appears.

5. Select ShowUserForm from the list of macros, and then click Run. The UserForm appears.

6. Make your choice, and click OK.

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

The code does have a problem, though: if you select one or more entire columns, the procedure processes every cell, which can take a long time. The version of the workbook on the website corrects this problem by working with a subset of the selection that intersects with the workbook's used range.

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:

1. Choose Developer Controls Insert and click theButtoncontrol in the Form Controls group.

2. Click and drag in the worksheet to create the button. The Assign Macro dialog box appears.

3. Select the ShowUserForm macro, and then click OK.

4. (Optional) 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 you perform 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.

Making the macro available on your Quick Access toolbar

If you'd like to use this macro while other workbooks are active, you may want to add a button to your Quick Access toolbar. Follow these steps:

1. Make sure that the workbook containing the macro is open.

2. Right-click anywhere on the Ribbon and choose Customize Quick Access Toolbar from the shortcut menu. The Excel Options dialog box appears, with the Quick Access Toolbar section selected.

3. Choose Macros from the Choose Commands From drop-down menu on the left. You'll see your macro listed.

4. Select the macro's name, and click Add to add the item to the list on the right.

5. (Optional) To change the icon, click Modify and choose a new image. You can also change the Display Name.

6. Click OK to close the Excel Options dialog box. The icon appears on your Quick Access toolbar.

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.

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

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 (see Figure 41.12). 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.

Figure 41.12 Adjusting the tab order in a UserForm.

Learning More

Mastering UserForms takes practice. You should closely examine the dialog boxes that Excel uses to get a feeling for the way 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.