XL2000: Cannot Modify or Delete Custom Menus

When you customize a menu in Microsoft Excel 2000, the menu changes may not be saved when you save, close, and reopen the workbook.

Customizations to a menu in Microsoft Excel 2000 may not be saved with a workbook if the following conditions are true:

• The workbook was originally created in either Microsoft Excel version 5.0 or 7.0.
  
  -and-
• You created custom menus in this workbook using the Menu Editor in either Microsoft Excel version 5.0 or 7.0.
  
  -and-
• You have saved your workbook in the Microsoft Excel 2000 format.

You cannot save modifications (made in Microsoft Excel 2000) to custom menus you create in versions of Microsoft Excel earlier than Excel 97. If you want to remove custom menus created in earlier versions of Microsoft Excel, copy the contents of your workbook to a new Microsoft Excel 2000 workbook. Use the Move or Copy Sheet command on the Edit menu to copy the sheets. The custom menus are not copied to the new workbook.

Steps to Copy All Sheets to a New Workbook

1. Close all open workbooks and open the workbook that contains the custom menu.
2. Right-click a sheet tab, and then click Select All Sheets.
   
   NOTE: If the workbook is protected, you must first unprotect it. To do this, point to Protection on the Tools menu, and then click Unprotect Workbook. Type the password if you are prompted to do so.
3. On the Edit menu, click Move or Copy Sheet. In the To book list, click (new book). Select the Create a copy check box and click OK. This will copy all sheets to a new workbook and activate the new workbook.
4. If there are not any macros in the original workbook continue with step 10, otherwise activate the original workbook that contains the macros. Click the workbook name on the Window menu to accomplish this.
5. On the Tools menu, point to Macro, and then click Visual Basic Editor.
6. On the View menu, click Project Explorer. Click the plus sign (+) next to the Modules folder to display all modules in the project.
7. In the Project Explorer, drag each module from the Modules folder of the original workbook to the new workbook created in step 3.
8. On the File menu, click Close and Return to Microsoft Excel.
9. On the File menu, click Close to close the original workbook.
10. Save the new workbook. This workbook will now allow you to modify the menu.

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

In Microsoft Excel 2000, you do not use the Menu Editor in the same way as you do in versions of Microsoft Excel earlier than Excel 97. To work with menus in Microsoft Excel 2000, read each of the sections listed below.

Customize Existing Menus

This example will customize the Window menu on the worksheet menu bar.

1. On the Tools menu, click Customize.
   
   You will now be able to customize the menus in Microsoft Excel 2000.
2. When the Customize dialog box appears, click Window on the worksheet menu bar.
3. Right-click Split and then click Delete on the shortcut menu.
4. Click Close in the Customize dialog box.
   
   If you click Window, the Split menu item is not on the menu.
5. On the Tools menu, click Customize.
6. When the Customize dialog box appears, right-click Window on the worksheet menu bar.
7. Click Reset on the shortcut menu that appears.
8. Click Close in the Customize dialog box.

If you click Window, the Split menu item is back on the menu.

Creating a New Custom Menu

1. On the Tools menu, click Customize.
2. In the Customize dialog box, click the Commands tab.
3. In the Categories list, scroll down to the bottom of the list and click New Menu.
   
   On the right side of the Commands tab, New Menu is listed under Commands.
4. Drag New Menu to the worksheet menu bar and place it to the right of the Help menu.
   
   NOTE: When you move New Menu up to the worksheet menu bar, an insertion line becomes visible to assist you in locating your new menu.
5. In the Categories list, click Macros.
   
   On the right side of the Commands tab, you now see Custom Menu Item and Custom Button under Commands.
6. Drag Custom Button and place it (do not drop it yet) on top of New Menu that now appears on the Worksheet Menu Bar.
7. A small empty menu appears below New Menu. Drop the Custom Button on this small empty menu.
   
   NOTE: The insertion marker should be in the empty menu that appears below New Menu in order for Custom Button to become a menu item on New Menu.
8. Right-click Custom Button, which is now a menu item on New Menu.
   
   A shortcut menu appears with a list of all the changes you can make to this menu item. The following is a partial list of the things you can do:
   • Change the name to something other than Custom Button
   • Change the button image on this menu item
   • Assign a macro to this menu item
   • Delete the menu item
9. Click Close on the Customize dialog box to finish customizing your menus.

For more information about customizing menus, click Microsoft Excel Help on the Help menu, type Customize menus and toolbars in the Office Assistant or the Answer Wizard, and then click Search to view the topic.