This chapter is from the book
This chapter is from the book
This chapter is from the book
Running a Macro
If you assigned a shortcut key to your macro, you can play it by pressing the key combination. Macros can also be assigned to toolbar buttons, forms controls, drawing objects, or you can run them from the Visual Basic toolbar.
Creating a Macro Button on the Ribbon
You can add an icon to a new group on the Ribbon to run your macro. This is appropriate for macros stored in the Personal Macro Workbook. Follow these steps to add a macro button to the Ribbon:
- Click the File menu and select Excel Options to open the Excel Options dialog.
- In the Excel Options dialog, select the Customize Ribbon category from the left-side navigation.
- In the list box on the right, choose the tab name where you want to add an icon.
- Click the New Group button below the right list box. Excel adds a new entry called New Group (Custom) to the end of the groups in that ribbon tab.
- To move the group to the left in the ribbon tab, click the up-arrow icon on the right side of the dialog several times.
- To rename the group, click the Rename button. Type a new name, such as Report Macros. Click OK. Excel will show the group in the list box as Report Macros (Custom). Note that the word Custom will not appear in the Ribbon.
- Open the upper-left drop-down and choose Macros from the list. The Macros category is fourth in the list. Excel displays a list of available macros in the left list box.
- Choose a macro from the left list box. Click the Add button in the center of the dialog. Excel moves the macro to the right list box in the selected group. Excel uses a generic VBA icon for all macros. You can change the icon by following steps 9 and 10.
- Click the macro in the right list box. Click the Rename button at the bottom of the right list box. Excel displays a list of 180 possible icons. Choose an icon. Alternatively, type a friendly label for the icon, such as Format Report.
- Click OK to close Excel options. The new button appears on the selected Ribbon tab.
Creating a Macro Button on the Quick Access Toolbar
You can add an icon to the Quick Access toolbar to run your macro. If your macro is stored in the Personal Macro Workbook, you can have the button permanently displayed in the Quick Access toolbar. If the macro is stored in the current workbook, you can specify that the icon should appear only when the workbook is open. Follow these steps to add a macro button to the Quick Access toolbar:
- Click the File menu and select Excel Options to open the Excel Options dialog.
- In the Excel Options dialog select the Customize category from the left-side navigation.
- If your macro should be available only when the current workbook is open, open the upper-right drop-down and change For All Documents (Default) to For <FileName.xlsm>. Any icons associated with the current workbook are displayed at the end of the Quick Access toolbar.
- Open the upper-left drop-down and select Macros from the list. The Macros category is fourth in the list. Excel displays a list of available macros in the left list box.
- Choose a macro from the left list box. Click the Add button in the center of the dialog. Excel moves the macro to the right list box. Excel uses a generic VBA icon for all macros. You can change the icon by following steps 6 through 8.
- Click the macro in the right list box. Click the Modify button at the bottom of the right list box. Excel displays a list of 180 possible icons (see Figure 1.5).
Figure 1.5 Attach a macro to a button on the Quick Access toolbar.
- Choose an icon from the list. In the Display Name box, replace the macro name with a short name that will appear in the ToolTip for the icon.
- Click OK to close the Modify Button dialog.
- Click OK to close Excel options. The new button appears on the Quick Access toolbar.
)
Assigning a Macro to a Form Control, Text Box, or Shape
If you want to create a macro specific to a workbook, store the macro in the workbook and attach it to a form control or any object on the sheet.
Follow these steps to attach a macro to a form control on the sheet:
- On the Developer tab, click the Insert button to open its drop-down list. Excel offers 12 form controls and 12 ActiveX controls. Many icons look similar in this drop-down. Click the Button Form Control icon at the upper-left icon in the drop-down.
- Move your cursor over the worksheet; the cursor changes to a plus sign.
- Draw a button on the sheet by clicking and holding the left mouse button while drawing a box shape. Release the button when you have finished.
- Choose a macro from the Assign Macro dialog box and click OK. The button is created with generic text such as Button 1. To customize the text or the button appearance, follow steps 5 through 7.
- Type a new label for the button. Note that while you are typing, the selection border around the button changes from dots to diagonal lines to indicate that you are in Text Edit mode. You cannot change the button color while in Text Edit mode. To exit Text Edit mode, either click the diagonal lines to change them to dots or Ctrl-click the button again. Note that if you accidentally click away from the button, you should Ctrl+click the button to select it. Then drag the cursor over the text on the button to select the text.
- Right-click the dots surrounding the button and select Format Control. Excel displays the Format Control dialog with seven tabs across the top. If your Format Control dialog has only a Font tab, you failed to exit Text Edit mode. If this occurred, close the dialog, Ctrl-click the button, and repeat this step.
- Use the settings in the Format Control dialog to change the font size, font color, margins, and similar settings for the control. Click OK to close the Format Control dialog when you have finished. Click on a cell to unselect the button.
- Click the button to run the macro.
Macros can be assigned to any worksheet object such as clip art, a shape, SmartArt graphics, or text box. In Figure 1.6, the top button is a traditional button form control. The other images are clip art, a shape with WordArt, and a SmartArt graphic. To assign a macro to any object, right-click the object, and select Assign Macro.
)
Figure 1.6 Assigning a macro to a form control or an object appropriate for macros stored in the same workbook as the control. You can assign a macro to any of these objects.