The ribbon enables you to discover the commands built into Excel
quickly. However, 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 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
The Quick Access Toolbar page of the Excel Options dialog box.
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
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, which saves a significant amount
of time compared to displaying the View tab and moving the mouse to the
far right edge of the ribbon.
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 The Quick Access Toolbar page of the Excel Options
dialog box. From there, in the Choose Commands From list, 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 symbol you want, type a new text
value to appear when a user points to 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 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 on the shortcut menu that opens. In the Assign Macro dialog
box, click the macro you want to run when you click the shape, and then
click OK.
Important
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 from running the macro.
In this exercise, you’ll 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.
Set Up
Open the
PerformanceDashboard_start workbook, click the Enable Content button
on the Message Bar (if necessary), and save the workbook as
PerformanceDashboard. Then follow the
steps.
-
On the Quick Access Toolbar, click the Customize Quick Access Toolbar button, and
then click More Commands.
The Customize The Quick Access Toolbar page of the Excel
Options dialog box opens, displaying the Popular Commands category
in the Choose Commands From pane.
-
In the list of available commands, click View Macros.
-
Click Add.
The View Macros command appears in the Customize Quick Access
Toolbar pane.
-
In the Choose commands from
list, click Macros.
The available macros appear in the pane below.
-
In the Choose commands from
pane, click SavingsHighlight.
Troubleshooting
If macros in the workbook are not enabled, the
SavingsHighlight macro will not appear in the list.
-
Click Add.
The SavingsHighlight macro appears in the Customize Quick
Access Toolbar pane.
-
In the Customize Quick Access
Toolbar pane, click the SavingsHighlight command.
-
Click Modify.
The Modify Button dialog box opens.
-
Click the blue button with the white circle inside it (the
fourth button from the left on the top row).
-
Click OK twice to close the
Modify Button dialog box and the
Excel Options dialog box.
The Excel Options dialog box closes, and the View Macros and
SavingsHighlight buttons appear on the Quick Access Toolbar.
-
On the worksheet, right-click the Show
Efficiency shape, and then click Assign Macro.
The Assign Macro dialog box opens.
-
Click EfficiencyHighlight,
and then click OK.
The Assign Macro dialog box closes.
-
On the Quick Access Toolbar, click the SavingsHighlight button.
Excel runs the macro, which applies a conditional format to
the values in the Savings column of the table on the left.
-
Click the Show
Efficiency shape.
Excel runs the macro, which applies a conditional format to
the values in the Efficiency column of the table on the
right.
-
On the Quick Access Toolbar, click the Save button to save your work.
Clean Up
Close the PerformanceDashboard workbook.
