Part VI: Programming Excel with VBA

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

On the CD

This workbook is available on the companion CD-ROM. 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 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 the UserForm object:

Property	Change To
Caption	Case Changer

4.Add a CommandButton object to the UserForm and then change the following properties for the CommandButton:

Property

Change To

Name OKButton

Caption OK

Default True

5. Add another CommandButton object and then change the following properties:

Property

Change To

Name CancelButton

Caption Cancel

Cancel True

850

Chapter 41: Creating UserForms

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

Name OptionUpper

Caption

Upper Case

Value True

7. Add a second OptionButton control and then change the following properties:

Property

Change To

Name OptionLower

Caption

Lower Case

8. Add a third OptionButton control and then change the following properties:

Property

Change To

Name OptionProper

Caption

Proper Case

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.

851

Part VI: Programming Excel with VBA

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

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:

Sub ShowUserForm() UserForm1.Show

End Sub

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

FIGURE 41.11

Displaying the UserForm.

852

Chapter 41: Creating UserForms

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 the CancelButton control. The VB Editor activates the code module for the UserForm and inserts an empty procedure.

3.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:

1.Select OKButton from the drop-down list at the top of the module. The VB Editor begins a new procedure called OKButton_Click.

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

853

Part VI: Programming Excel with VBA

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

Testing the UserForm

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

1.Activate Excel.

2.Enter some text into some cells.

3.Select the range with the text.

4.Choose Developer Code Macros (or press Alt+F8).

5.In the Macros dialog box, select ShowUserForm from the list of macros and then click OK. The UserForm appears.

6.Make your choice and click OK.

854

Chapter 41: Creating UserForms

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

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 the Button control in the Form

Controls group.

2.Click and drag in the worksheet to create the button.

3.Excel displays the Assign Macro dialog box.

4.In the Assign Macro dialog box, select ShowUserForm and then click OK.

5.(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 performing the preceding steps, clicking the button executes the macro and displays the UserForm.

Cross-Reference

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

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:

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.

855