Excel 2010 Bible

Part VI: Programming Excel with VBA

810

If you store macros in the Personal Macro Workbook, you don’t have to remember to open the

Personal Macro Workbook when you load a workbook that uses macros. When you want to exit,

Excel asks whether you want to save changes to the Personal Macro Workbook.

Assigning a macro to a shortcut key

When you begin recording a macro, the Record Macro dialog box gives you an opportunity to pro-

vide a shortcut key for the macro. Here’s what to do if you’d like to change the shortcut key or

provide a shortcut key for a macro that doesn’t have one:

1. Choose Developer ➪ Code ➪ Macros (or press Alt+F8) to display the Macro
   dialog box.


2. In the Macro dialog box, select the macro name from the list.


3. Click the Options button, and Excel displays its Macro Options dialog box. See
   Figure 39.11.


4. Specify the shortcut key. Use a single letter (for a Ctrl+letter shortcut), or press Shift
   and enter an uppercase letter (for a Ctrl+Shift+letter shortcut).


5. Click OK to return to the Macro dialog box.


6. Click Cancel to close the Macro dialog box.

FIGURE 39.11

Use the Macro Options dialog box to add or change a shortcut key for a macro.

Assigning a macro to a button

After you record a macro and test it, you may want to assign the macro to a button placed in a

worksheet. You can follow these steps to do so:

1. If the macro is a general-purpose macro that you plan to use in more than one work-
   book, make sure that the macro is stored in your Personal Macro Workbook.


2. Choose Developer ➪ Controls ➪ Insert and then click the Button control from the
   Form Controls section (see Figure 39.12). Move your mouse pointer over the icons,
   and you see a ToolTip that describes the control.