Adding Submenus and Submenu Items

A submenu (child menu) is a menu attached to the right side of a menu item (the submenu caption) on another menu (the parent menu). Submenus can be added to both drop-down menus and shortcut menus.

Note

A submenu caption is a menu item on the submenu's parent menu. Any commands, separator bars, or submenu captions on the submenu are also menu items; the submenu is their parent menu.

Just as you display the items on a drop-down menu by clicking the menu caption on the menu bar,you display items on a submenu by pointing to the submenu caption on the parent menu. Similarly, just as you first add an empty drop-down menu (containing a caption but no menu items) to a menu bar and then add individual menu items, you first add an empty submenu to a parent menu and then add menu items.

You can add submenu items to existing custom or built-in submenus, but you cannot add submenu items to built-in commands. For example, you can add a submenu item to the Record Macro submenu on the Tools menu, but not to the Macro command on the Tools menu. You can, however, delete a built-in command, add a custom submenu caption with the same name as the built-in command, and then add submenu items to the custom submenu.

Using the Menu Editor

To add a custom submenu using the Menu Editor, you start by adding the submenu caption as a new menu item on the parent menu. To do this, simply follow the instructions in the preceding section for adding a command to a menu, and add the submenu caption as a menu item. You don't need to specify a procedure in the Macro box. After you've added the submenu caption, you can add the submenu items. You can also add submenu items to an existing custom submenu or custom command (after you add a submenu item to a custom command, the command becomes a submenu caption).

To add a submenu item

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 submenu or the object for which a shortcut menu is defined.

4. In the Menu Items box, click the name of the menu item to which you want to add the submenu item.

5. In the Submenu Items box, click the name of the submenu item above which you want to insert the new submenu item.

– Or –

To insert a submenu item at the bottom of the submenu, click End Of Submenu.

6. Choose Insert to create the new submenu item.

7. In the Caption box, type the name of the new submenu item.

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

To create a separator bar, type one hyphen (-).

8. To specify that a particular procedure run whenever the user chooses the submenu item, enter the name of the procedure in the Macro box.

Using Visual Basic

Adding a submenu to a menu using Visual Basic entails two main steps. First, you add the submenu caption to the parent menu, and then you add submenu items to the submenu.

The AddMenu method of the MenuItems collection adds a submenu caption. You cannot add submenu items to a command or separator bar created with the Add method. (This is different from the Menu Editor, where you use the same technique to add commands, separator bars, and submenu captions.)

The following example creates a custom menu.

MenuBars(xlModule).Menus.Add "myMenu"

The following example adds a submenu caption to a menu.

MenuBars(xlModule).Menus("myMenu").MenuItems.AddMenu "mySM"

Adding items to a submenu can be confusing because you must apply the MenuItems method once to access the submenu caption (a menu item on the parent menu), and then you must apply the method again to access the collection of menu items on the submenu. The following example adds a menu item to the submenu created in the preceding example.

MenuBars(xlModule).Menus("myMenu").MenuItems("mySM") _
    .MenuItems.Add "mySMItem"

The following example places a check mark next to a menu item on a submenu.

MenuBars(xlModule).Menus("myMenu").MenuItems("mySM") _
    .MenuItems("mySMItem").Checked = True

Note

The MenuItems method returns a MenuItem object when the menu item is a command or a separator bar, and it returns a Menu object when the menu item is a submenu caption.

The following example combines the preceding four examples into one.

MenuBars(xlModule).Menus.Add("myMenu").MenuItems.AddMenu("mySM") _
    .MenuItems.Add("mySMItem").Checked = True

The following example adds a new menu to the Worksheet menu bar and then adds several new menu items and a submenu to the new menu.

Sub AddNewMenu()
    Set sortMenu = MenuBars(xlWorksheet).Menus.Add("&Sort By")
    With sortMenu.MenuItems
        .Add "Company Name", "SortByCompany"
        .Add "Category ID", "SortByCategory"
        .Add "Type", "SortByType"
        .Add ("-")
        .Add "Random", "SetRandomOrder"
        Set sortSubMenu = sortMenu.MenuItems _
                .AddMenu("Cost", before:="-")
            sortSubMenu.MenuItems.Add "Ascending", "SortByCostAscending"
            sortSubMenu.MenuItems.Add "Descending", _
                "SortByCostDescending"
    End With
End Sub