Attaching a Macro to a Toolbar

As you build macros, you might not remember the macro names or even their shortcut names. You can use Tools, Macro, Run to choose the macros, but this method requires you to continually pull down the menu and scroll through the list of names in the Macro dialog box. A more efficient way to run a macro is to assign it to a button on a toolbar. Assigning a macro to a button makes the macro run whenever you click the button with your mouse. Attaching macros to a toolbar is a quick way to organize your macros so that any user can easily run them.

The next To Do exercise shows you how to attach a macro to the Standard toolbar for the Detail sheet.

To Do: Attach a Macro to a Toolbar

  1. In the Detail sheet, choose Tools, Customize. The Customize dialog box appears.

  2. Click the Toolbars tab. The Standard toolbar check box should have a check mark in it. The check mark means that the Standard toolbar is currently displayed, and you can make changes to it. You're going to attach the macro to this toolbar.

  3. If the button you want to run the macro from is not on a toolbar, click the Commands tab and then click Macros in the Categories list.

  4. In the Commands list, you should see the Custom Button with a smiley face next to it, as shown in Figure 20.6. Drag the Custom button onto the Standard toolbar, placing the button between the Zoom box and the Help button at the far right end of the toolbar. You should see a thick border surrounding the Custom button with the yellow smiley face on it. Close the Custom dialog box.

    Figure 20.6. The Macros category and Custom Button command.


  5. Click the Custom toolbar button. The Assign Macro dialog box opens, as shown in Figure 20.7.

    Figure 20.7. The Assign Macro dialog box.


  6. Click the Font_change macro name. This step enters the name of the macro in the Macro Name box.

  7. Click OK. Excel attaches the macro to the Custom button on the Standard toolbar. Click the Close button to close the Customize dialog box. Now you want to test the new macro button on the toolbar.

  8. Select cell A4. Click Custom Button on the Standard toolbar. Bingo! Excel changes the font and font size for the data you selected on the worksheet. You should see the 28-point Arial font.


    Another way to run a macro is to assign it to a graphic object on the worksheet. To do so, right-click on the graphic, and choose Assign Macro. In the Assign Macro dialog box, select a macro in the Macro Name list and click OK. Click any cell to deselect the graphic object. Now when you move the mouse pointer over the graphic, the pointer is a hand pointer. Click the graphic and Excel runs the macro you assigned to the graphic.

    Part I: Excel Basics