Menu Object

Description

Represents a drop-down menu, shortcut menu, or submenu.

Accessors

The Menu object is a member of the Menus collection. The Menus collection contains all of the menus on a menu bar. Use the Add method to create a new menu and add it to the specified menu bar. To access a single member of the collection, use the Menus method with the menu caption or index number as an argument.

The index number indicates the position of the menu on the menu bar. The following example adds a new menu item to the bottom of the File menu on the Worksheet menu bar.

MenuBars("worksheet").Menus(1).MenuItems.Add "Search"

The menu caption is the text that appears in the menu bar. Use the Caption property to set or return the menu caption. The following example changes the caption for the Help menu on the Visual Basic Module menu bar.

MenuBars(xlModule).Menus("help").Caption = "HELP!"

The following example creates a table on worksheet one that contains the captions of all the menus on all the menu bars in the application. The column headings are the menu bar captions, and the column entries under each heading are the menu captions on that menu bar.

Sub EnumerateMenuBars()
    Worksheets(1).Activate
    c = 1
    For Each mb In MenuBars
        Cells(1, c) = mb.Caption
        i = 2
        For Each mn In mb.Menus
            Cells(i, c) = mn.Caption
            i = i + 1
        Next
        c = c + 1
    Next
End Sub

The MenuItems method returns a Menu object when the specified menu item is a submenu. The following example sets the Checked property for the mySubMenuItem submenu item on the mySubMenu submenu.

MenuBars(xlModule).Menus("myMenu").MenuItems("mySubMenu") _
    .MenuItems("mySubMenuItem").Checked = True

Remarks

You should use the menu name instead of its index number so your code will not depend on the current menu layout. Other Visual Basic procedures may change the menu layout, and the layout may change in future versions of Microsoft Excel. It is safer to use Menubars(xlWorksheet).Menus("File") than to rely on the File menu being the first menu on the menubar and use Menubars(xlWorksheet).Menus(1).

Properties

Application Property, Caption Property, Creator Property, Enabled Property, Index Property, Parent Property.

Methods

Delete Method, MenuItems Method.