Adding Commands and Separator Bars

You can add commands and separator bars to any built-in or custom menu. This section deals with adding these items to drop-down menus and shortcut menus. The following section addresses the specific issues of adding submenus and then adding menu items to those submenus.

Using the Menu Editor

The Menu Editor offers an easy method for adding items to menus.

To add a menu item to a drop-down menu or shortcut menu

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 a menu bar or a group of shortcut menus.

3. In the Menus box, click the name of the menu to which you want to add the menu item or the object for which a shortcut menu is defined.

4. In the Menu Items box, click the name of the menu item above which you want to insert the new menu item.

– Or –

To add a menu item to the bottom of the menu, click End Of Menu.

5. Click Insert to create the new menu item.

6. In the Caption box, type a name for the new menu item.

Type an ampersand (&) before the character you want to use as the access key for the new menu item. To create a separator bar, type one hyphen (-).

7. To specify that a particular procedure run whenever the user clicks the new menu item, enter the name of the procedure in the Macro box.

Using Visual Basic

Use the Add method of the MenuItems collection to add a new menu item to the specified menu. You can specify the menu item caption, its position on the menu, and the name of the procedure to run when the user clicks the menu item.

The following example adds an Open Database menu item to the File menu on the Worksheet menu bar. Microsoft Excel runs the OpenDatabaseProc Visual Basic procedure whenever the user clicks the menu item. Open Database appears directly above the Close item on the File menu.

Set databaseItem = MenuBars(xlWorksheet).Menus("File") _
    .MenuItems.Add(Caption:="Open &Database", _
        OnAction:="OpenDatabaseProc", _
        before:="Close")

To place the menu item at the end of the specified menu, omit the before argument.

To create a separator bar, specify "-" for the new menu caption, as shown in the following example.

MenuBars(xlWorksheet).Menus("File").MenuItems.Add _
    Caption:="-", before:="Close"

You cannot create a new shortcut menu, but you can modify a built-in shortcut menu by adding or deleting menu items. The following example adds a Format Special menu item at the end of the Worksheet Cell shortcut menu. Notice that although shortcut menus are displayed as groups in the Menu Editor, those menu groups don't actually belong to the MenuBars collection. To access shortcut menus, you must use the ShortcutMenus method instead of the MenuBars method.

ShortcutMenus(xlWorksheetCell).MenuItems.Add _
    Caption:="Format &Special", OnAction:="FormatSpecialProc"