Running Macros When a Button Is Clicked

The new Ribbon user interface enables you to discover the commands built into Excel 2007 quickly, but it can take a few seconds to display the View tab, open the Macro dialog box, select the macro you want to run, and click the Run button. When you're in the middle of a presentation, taking even those few seconds can reduce your momentum and force you to regain your audience's attention. Excel 2007 offers several ways for you to make your macros more accessible.

If you want to display the Macro dialog box quickly, you can add the View Macros button to the Quick Access Toolbar. To do so, click the Customize Quick Access Toolbar button at the right edge of the Quick Access Toolbar and then click More Commands to display the Customize tab of the Excel Options dialog box.

See Also

For more information on customizing the Quick Access Toolbar, see Chapter 2, "Setting Up a Workbook."

When you display the Popular Commands command group, you'll see that the last item in the command pane is View Macros. When you click the View Macros item, click the Add button, and then click OK, Excel 2007 adds the command to the Quick Access Toolbar and closes the Excel Options dialog box. Clicking the View Macros button on the Quick Access Toolbar displays the Macro dialog box without having to display the View tab and move the mouse to the far right edge of the ribbon, saving you a significant amount of time.

If you prefer to run a macro without having to display the Macro dialog box, you can do so by adding a button representing the macro to the Quick Access Toolbar. Clicking that button runs the macro immediately, which is very handy when you create a macro for a task you perform frequently. To add a button representing a macro to the Quick Access Toolbar, click the Customize Quick Access Toolbar button at the right edge of the Quick Access Toolbar and then click More Commands to display the Customize tab of the Excel Options dialog box. From there, click the Choose Commands From box down arrow and click Macros. Click the macro you want represented on the Quick Access Toolbar, click Add, and then click OK.

If you add more than one macro button to the Quick Access Toolbar or if you want to change the button that represents your macro on the Quick Access Toolbar, you can select a new button from more than 160 options. To assign a new button to your macro, click the macro item in the Customize Quick Access Toolbar pane and click the Modify button to display your choices. Click the button you want, type a new text value to appear when a user's mouse pointer hovers over the button, and then click OK twice (the first time to close the Modify Button dialog box and the second to close the Excel Options dialog box).

Finally, you can have Excel 2007 run a macro when you click a shape in your workbook. Assigning macros to shapes enables you to create "buttons" that are graphically richer than those available on the Quick Access Toolbar. If you're so inclined, you can even create custom button layouts that represent other objects, such as a remote control. To run a macro when you click a shape, right-click the shape and then click Assign Macro from the shortcut menu that appears. In the Assign Macro dialog box, click the macro you want to run when you click the shape and then click OK.

Warning

When you assign a macro to run when you click a shape, don't change the name of the macro that appears in the Assign Macro dialog box. The name that appears refers to the object and what the object should do when it is clicked; changing the macro name breaks that connection and prevents Excel 2007 from running the macro.

In this exercise, you will add the View Macros button to the Quick Access Toolbar, add a macro button to the Quick Access Toolbar, assign a macro to a workbook shape, and then run the macros.

USE the Performance Dashboard workbook in the practice file folder for this topic. This practice file is located in the My Documents\Microsoft Press\Excel SBS\Macros folder.

OPEN the Performance Dashboard workbook.

1.	On the Quick Access Toolbar, click the Customize Quick Access Toolbar button and then click More Commands. The Customize page of the Excel Options dialog box appears.
2.	If necessary, click the Choose commands from down arrow and then click Popular Commands. The commands in the Popular Commands category appear.
3.	In the Commands panel, click View Macros.
4.	Click Add. The View Macros command appears in the Customize Quick Access Toolbar command panel.
5.	Click the Choose commands from box down arrow and then click Macros. The available macros appear.
6.	In the Commands panel, click SavingsHighlight.
7.	Click Add. The SavingsHighlight macro appears in the Customize Quick Access Toolbar command panel.
8.	In the Customize Quick Access Toolbar command panel, click the SavingsHighlight command.
9.	Click Modify. The Modify Button dialog box appears.
10.	Click the blue button with the white circle inside it (the fourth button from the left on the top row).
11.	Click OK twice to close the Modify Button dialog box and the Excel Options dialog box. The Excel Options dialog box disappears, and the View Macros and SavingsHighlight buttons appear on the Quick Access Toolbar.
12.	Right-click the Show Efficiency shape and then click Assign Macro. The Assign Macro dialog box appears.
13.	Click EfficiencyHighlight and then click OK. The Assign Macro dialog box disappears.
14.	On the Quick Access Toolbar, click the SavingsHighlight button. Excel 2007 runs the macro, which applies a conditional format to the values in the Savings column of the left-hand table.
15.	Click the Show Efficiency shape. Excel 2007 runs the macro, which applies a conditional format to the values in the Efficiency column of the right-hand table.
16.	On the Quick Access Toolbar, click the Save button to save your work.

CLOSE the Performance Dashboard workbook.