Deleting a menu

In some cases, you may want to delete one or more Excel built-in menus while a particular workbook is open, or delete a custom menu that you added.

The following statements delete the Help menu for the Worksheet menu bar and the Chart menu bar:

Application.CommandBars(1).Controls("Help").Delete Application.CommandBars(2).Controls("Help").Delete

You get an error message if the Help menu doesn't exist. Therefore, you might want to precede the statements with the following statement, which causes Excel to ignore any errors:

On Error Resume Next

You can restore in one of two ways built-in menu items that you deleted:

1 Reset the entire menu bar.

1 Use the Add method to add the built-in menu and then add all the menu items. You may want to record your actions while you use the Customize dialog box to restore a menu.

The following statements reset the Worksheet menu bar and the Chart menu bar:

Application.CommandBars(1).Reset Application.CommandBars(2).Reset

Resetting a menu bar destroys any customization you may have performed. For example, if you added a new menu item to the Tools menu, that menu item is removed when the menu bar is reset.

0 0

Post a comment