Enabling or Disabling Menu Components

If you want to prevent the user from choosing a particular menu item under certain conditions, you can disable it. A disabled command still appears on the menu, but it appears dimmed and doesn't respond to user actions. Use the Enabled property to enable or disable a menu item. The Enabled property is True if the menu item is enabled, and it's False if the menu item is disabled (you cannot set the Enabled property for a built-in menu item). The following example adds the Open Database menu item to the File menu on the Worksheet menu bar and then disables Open Database.

MenuBars(xlWorksheet).Menus("File") _
    .MenuItems.Add("Open Database").Enabled = False

If you want to disable all the commands on a drop-down menu, you can disable the menu itself. This effectively disables all the commands on the menu, as the user cannot access them. The following example disables the entire File menu on the Worksheet menu bar.

MenuBars(xlWorksheet).Menus("File").Enabled = False

Note

You can disable all the menu items on a submenu, but you cannot disable the submenu itself.

The following example disables all the menu items on the Cost submenu that was created by the example in "Adding Submenus and Submenu Items" earlier in this chapter.

With MenuBars(xlWorksheet).Menus("sort by").MenuItems("cost")
    For Each mnItem In .MenuItems
        mnItem.Enabled = False
    Next
End With

You can enable or disable a shortcut menu by setting the Enabled property for menu item 0 (zero). The following example disables the Worksheet Cell shortcut menu.

ShortcutMenus(xlWorksheetCell).MenuItems(0).Enabled = False