Restoring Built-in Menu Components

You can restore built-in menu bars, menus, or menu items that you've deleted. However, you cannot restore custom menu bars, menus, or menu items that you've deleted; you must recreate them.

Using the Menu Editor

If you want to restore built-in components that you've deleted using the Menu Editor, you must open the Menu Editor while the workbook where you originally made the deletions is active. You can restore menu components one at a time, or you can restore the entire default menu system for the active workbook at once. The Menu Editor cannot restore components deleted with Visual Basic code.

To restore a deleted menu item

1. While a Visual Basic module is active, click Menu Editor on the Tools menu.

2. Select the Show Deleted Items check box.

Deleted menu items appear dimmed.

3. In the appropriate box, select the deleted item that you want to restore.

4. Click Undelete.

To restore all the built-in menu bars, menus, menu items, and submenu items (that is, the entire default menu system) at once, click Restore All.

Caution

Clicking Restore All not only restores deleted built-in components, but it also deletes from the active workbook every custom menu bar, menu, menu item, and submenu item that was added with the Menu Editor. Unless you're absolutely sure that there are no custom menu components you want to save, you should restore any deleted components one by one, using the foregoing procedure.

Using Visual Basic

Use the Reset method of the MenuBars collection to restore a built-in menu bar and all the menu components it contains to their default configuration.

MenuBars(xlWorksheet).Reset

Caution

Be careful when you use this method; the Reset method not only restores any deleted built-in components of the menu bar, but it also deletes any custom components that have been added. Keep in mind that another macro may have added custom components to the menu bar in question, and resetting the menu bar will remove these as well. In addition, resetting the menu bar may cause conflicts with the menu editing lists of open workbooks. To avoid these problems, remove any menu components that your macro has added (and restore any menu components that you've deleted) one by one, without resetting the entire menu bar.

Set the restore argument of the Add method to True to restore a previously deleted built-in drop-down menu, shortcut menu, or menu item. To restore a previously deleted built-in submenu, set the restore argument of the AddMenu method to True. The following example restores the previously deleted Edit menu to the Chart menu bar.

MenuBars(xlChart).Menus.Add caption:="Edit", _
    before:="View", restore:=True

This example restores the previously deleted Open menu item to the File menu on the Worksheet menu bar.

MenuBars(xlWorksheet).Menus("File").MenuItems.Add _
    caption := "Open...", _
    restore := True, _
    before := "Close"

This example restores the previously deleted Protection submenu to the Tools menu on the Visual Basic Module menu bar.

MenuBars(xlModule).Menus("Tools").MenuItems.AddMenu _
    caption := "Protection", _
    restore := True, _
    before := "Add-Ins..."

Note

If you omit the restore argument, Visual Basic adds a custom command that has the same name as the deleted built-in command but lacks its built-in functionality. If you omit the before argument, Visual Basic places the restored command at the end of the menu, rather than in its original position.