Adding Menus

You can add a drop-down menu to any built-in or custom menu bar. Because Microsoft Excel displays different built-in menu bars when different sheet types are active (for instance, the Chart menu bar appears when you switch to a chart), you may have to add a command to more than one menu bar to make sure the user has access to the command regardless of what sheet type is active. For example, you might want to add a special Accounting menu to each menu bar so that employees in a company can run the corresponding macros from any sheet.

When you add a drop-down menu to a menu bar, you can specify an access key for the menu; the access key appears underlined when the menu is displayed. Although shortcut menus appear as "menu bar" groups in the Menu Editor, you can neither add custom shortcut menus to these menu bars nor delete built-in shortcut menus from them. You can, however, add items to or delete items from shortcut menus.

Using the Menu Editor

The Menu Editor provides a convenient way to add a drop-down menu to a built-in or custom menu bar.

To add a drop-down menu to a menu bar

1. While a Visual Basic module is active, click Menu Editor on the Tools menu.

2. In the Menu Bars box, click the name of the menu bar to which you want to add your new menu.

3. In the Menus box, click the name of the menu to the left of which you want to insert the new menu.

– Or –

To add a menu to the right of the rightmost menu on the menu bar, click End Of Menu Bar.

4. Click Insert to create the new menu.

5. In the Caption box, type the name of the new menu.

Type an ampersand (&) before the character you want to use as the access key for the menu.

Using Visual Basic

Use the Add method of the Menus collection to add a menu to the specified menu bar. The following example adds a new drop-down menu, myWorkMenu, to the left of the Help menu on the Chart menu bar.

Set newMenu = MenuBars(xlChart).Menus.Add( _
    Caption:="&MyWorkMenu", _
    before:="Help")

Note

You use an ampersand (&) in the menu name in front of the character that will be used as the access key for the menu. After the menu has been added, you can specify the menu name either with or without the ampersand when you reference the menu using the Menus method.