Disabling or hiding menus

When a menu or menu item is disabled, its text appears in a faint shade of gray, and clicking it has no effect. Excel disables its menu items when they are out of context. For example, the Links menu item on the Edit menu is disabled when the active workbook does not contain any links.

You can write VBA code to enable or disable both built-in and custom menus or menu items. Similarly, you can write code to hide menus or menu items. The key, of course, is tapping into the correct event.

The following procedures are stored in the code module for the ThisWorkbook object:

Private Sub Workbook_Open()

Call AddMenu End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Call DeleteMenu End Sub

Private Sub Workbook_Activate()

Call UnhideMenu End Sub

Private Sub Workbook_Deactivate()

Call HideMenu End Sub

When the workbook is opened, the AddMenu procedure is called. When the workbook is closed, the DeleteMenu workbook is called. Two additional event handler procedures are executed when the workbook is activated or deactivated. The UnhideMenu procedure is called when the workbook is activated, and the HideMenu procedure is called when the workbook is deactivated.

The HideMenu procedure sets the Visible property of the menu to False, which effectively removes it from the menu bar. The UnhideMenu procedure does just the opposite. The net effect is that the menu is visible only when the workbook is active. These procedures, which assume that the Caption for the menu is "Budgeting", are as follows:

Sub UnhideMenu()

CommandBars(1).Controls("Budgeting").Visible = True End Sub

Sub HideMenu()

CommandBars(1).Controls("Budgeting").Visible = False End Sub

To disable the menu rather than hide it, simply access the Enabled property instead of the Visible property.

This example is available on the companion CD-ROM.

0 0

Post a comment