Assign A Macro To A Menu

You can assign a macro to any existing Excel menu. If you do not want to use existing menus, you can even create a new menu. By assigning a macro to a menu, you make the macro as accessible as any menu option. Assigning macros to menus eliminates the need to remember the shortcut key required to launch the macro.

When you add a macro to a menu, it remains on the menu for all workbooks that you open in Excel. For that reason, you should assign a macro that exists in your Personal Macro Workbook to a menu to ensure that all workbooks can access the macros. The Personal Macro Workbook stores commonly used macros for the current user, and opens as a hidden file each time you run Excel. Because the Personal Macro workbook is always open, any workbook can use all of the macros it contains. To learn more about the Personal

Macro Workbook, see the section "An Introduction to Macros."

You can assign the macro to any available menu, however, to keep your macros easy to find, you may want to place them all on one custom menu. You can create a new Excel menu using the Customize dialog box. Of course, whatever menu you decide to use as a home for your macro must exist on the Excel window before you can add the macro option to it.

You add options to a menu by dragging them onto the menu from the Customize dialog box. In fact, you can modify menus only while the Customize dialog box displays. You can remove menu options in a similar fashion by dragging them from the menu back to the Customize dialog box.

ASSIGN A MACRO TO A MENU

ASSIGN A MACRO TO A MENU

—H Click Tools O Customi

■ The Customize dialog box appears.

S Drag the item to the desired menu.

—H Click Tools O Customi

■ The Customize dialog box appears.

—0 Click the Command tab.

—Q Click the Custom Menu Item option.

S Drag the item to the desired menu.

■ The menu expands and a line indicates your position in the menu.

Q Release the mouse button.

fed ra | You can create a custom menu for macros you place on a menu. This keeps all the macro references in one location and prevents clutter on the existing Excel menus. To create a new menu, open the Customize dialog as described in the steps on this page. On the Customize tab select the New Menu option as the desired category. A New Menu option displays as the available command. Click the New Menu option and drag it to the desired menu location. After you add the menu, you can right click it, and change the name, which makes it ready to receive your macros.

When you name a menu option you can also create a shortcut key that corresponds to the menu option. Similar to the shortcuts you create for macros, the menu option shortcut launches whatever command you assign to the menu option. Also, these shortcuts launch with the Alt key. To create a menu shortcut, you need to type a & before the character in the menu item name that corresponds to the shortcut key. For example, if you want Alt+T to launch the menu option "Determine Total," you place the & before the letter T: "Determine &Totals."

—H Right-click the menu option.

■ The Assign Macro dialog box appears.

—H Right-click the menu option.

Type the desired name for the macro menu option in the field.

■ The Assign Macro dialog box appears.

L-— Click the name of the macro you want to assign to the new menu option.

■ The macro runs each time you select the menu option.

0 0

Post a comment