Excel 2019 Bible

Chapter 44: Creating UserForms

44

5. Select ShowUserForm from the list of macros and then click Run. The UserForm
   appears, as shown in Figure 44.11.


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 proce-

dure 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 the Button control 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 45 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 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.