Tutorial 9: Different ways to action a macro
This tutorial takes you through some different ways of assigning macros in Excel.
You’ll need to have completed Tutorial 6 to do the practice activity, as you’ll need the MyTitle macro that was created. Alternatively you could create and save a different simple test macro.
Assigning a macro to an existing menu item
Assigning macros to menus and toolbars is done through the Customize tool. Here’s how to assign a macro to either an existing or new menu:
Step 1: Open the Customize dialog box
Select from the menu Tools/Customize/Commands/Macros as displayed in the figure below.
Customize Commands dialog box
Step 2: Assign the macro to a menu
1. In the Categories list, click Macros
2. In the Commands list, click Custom Menu Item.
3. Drag the Custom Menu Item to the menu where you want to place your new menu item.
4. Right Click on Custom Menu Item in the menu you have just positioned.
5. Change the items Name.
6. Use the Assign Macro option to assign your macro to the menu item.
Feedback
· You can assign a shortcut key to your menu item using the ampersand symbol (&). The shortcut key will be the letter after the symbol. The symbol can be placed anywhere in the name.
· Microsoft Excel assigns the name of the macro as a tool tip. This is another reason for using sensible names for your macros.
Assigning a macro to a new menu item
You may want to create a new menu rather than place macros in existing menus.
1 Open up the Customize dialog again. (Select Tools/Customize then click the Commands tab as shown below.
New Menu dialog box
2 Select the New Menu category
3 Drag the New Menu item from the Commands list to the menu bar
4 Right click on the New Menu to rename it
5 In the Categories box, click Macros
6 From the Commands list, drag Custom Menu Item to the empty box below the New Menu. This box will appear when you place the pointer over the New Menu, as shown in figure below.
New Menu empty entry area
Assigning a macro to a toolbar button
1 From the Tools menu, select Customize.
2 If the toolbar that contains the button is not visible click the Toolbars tab. Select the check box next to the toolbar name.
3 If the button you want to run the macro from is not already on a toolbar, click the Commands tab. Then click Macros in the Categories list. In the Commands list, drag the Custom Button item onto a toolbar.
4 Right-click the toolbar button, then click Assign Macro on the shortcut menu.
5 Select the name of the macro to assign to this button.
Note: To modify buttons you must have the Customize Dialog Box open.
Assigning a macro to a button or graphic control
You can assign a macro to a button, drawing object or a graphic control on a worksheet, so that when you click the button a macro will run automatically. Let’s do this now.
1 If the Forms toolbar is not visible then from the Tools menu, select Customize then click the Toolbars tab and select the check box next to the Forms toolbar name.
2 Create a button or graphic control on your spreadsheet from the Forms toolbar by selecting the visual item you want to create, then dragging a rectangle on the sheet with the mouse.
3 If you are creating a button or group object then you will be prompted immediately to select a macro to assign.
4 To change the macro assigned to a button or to assign a macro to a different type of object, Right-click on the object created. Then click Assign Macro on the shortcut menu.
5 To assign an existing macro to the button or graphic control, select the name of the macro from the list provided. (Otherwise press the Record button to create one.) Then click OK.
Note: If you assign a macro to a button or other object that is already in use as a hyperlink, the hyperlink information is deleted. From then on, clicking the button or object runs the macro instead.
Practice activity
In this activity you’ll assign a macro to a menu.
Use the MyTitle macro that you created in Tutorial 6 for this activity, or just create a simple test macro.
1 Assign the MyTitle macro on the Tools menu. Use the menu name of MyTitle.
2 Run the macro from the Tools menu.
3 Create a new custom toolbar called Title.
4 Place a custom toolbar button onto the new toolbar and assign the macro MyTitle.
5 Run the macro from Title toolbar.
6 Insert a picture/graphic object to your worksheet.
7 Assign the MyTitle macro to the picture/graphic object.
8 Run the macro from the graphic object.
The following figure shows the Forms toolbar and a newly created button with the Assign Macro dialog box nearby.
· To change the Macro assigned to the button you could later click on the button then Right-Click in the grey area which surrounds the button.
· To edit the text Click on the button itself and edit the label directly.
Assigning a macro to a new button
Feedback
Did you have success straight away?
If you used the Control Toolbox instead you would have not been able to directly assign a macro to an object. You would have to view the code and add the correct VBA statement to run the macro of the form Module1.MyTitle within the Sub – End Sub lines provided.
Controls created through the Control Toolbox are more flexible but also require a more planned approach to their code. For example renaming the object (not just changing the caption) will break any code assignments you may have requiring you to cut and paste or rename the Sub names to suit the new object name.
XXX
2005