How to Use the Menu Editor

In Microsoft Excel 5.0 and 7.0, you can customize menus and you can create your own menus with the Menu Editor.

Built-in menus and menu items are part of Microsoft Excel. Any changes you make to the menu system are stored in a menu-editing list for the active workbook. Each open workbook has its own menu-editing list. When several workbooks are open, the menus show a combination of the menu-editing lists from all the open workbooks.

NOTE: Microsoft Excel 97 and Microsoft Excel 98 do not use the methods described in this article.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

To Add a Set Print Area Command to the File Menu

1. In your Personal Macro Workbook, type the following procedure into a module sheet:

         Sub SetPrintArea()
            On Error Resume Next
            ActiveSheet.PageSetup.PrintArea = Selection.Address
         End Sub
   						

2. With a Visual Basic module active, click Menu Editor on the Tools menu.
3. In the Menu Bars box, click the name of a menu bar or the name of a group of shortcut menus. Click Worksheet in the Menu Bars list.
4. In the Menus box, click the name of the menu to which you want to add the menu item, or click the name of the object for which a shortcut menu is defined. For example, under Menus, click &File.
5. In the Menu Items box, click the name of the menu item above which you want to insert the new menu item. To add the new item to the bottom of the menu, click End Of Menu. Under Menu Items, click Print Pre&view.
6. Click Insert.
7. In the Caption box, type the name you want for the new menu item. Type Se&t Print Area.
   
   NOTE: Type an ampersand (&) before the character you want to use as the access key. To create a separator bar, type a hyphen (-). In this example, when the menu is displayed the "t" in "Set" will be underlined. You can use a letter for an access key only once per menu (that is, once you have used "t" you cannot use it again in that menu).
8. To specify the macro that you want to run when the user clicks the menu item, enter the name of the procedure in the Macro box. Click the "SetPrintArea" macro from the Macro list, and click OK.
9. Hide the Personal Macro Workbook.

To use the new menu item, select a range for the print area, and click Set Print Area on the File menu.

NOTE: On Microsoft Excel for Windows 95, version 7.0, there also is a built-in Print Area menu item.

To Add a New Menu, Menu Item, and Submenu

1. In your Personal Macro Workbook, type the following procedure in a module sheet:

         Sub ShowPageSetup()
            Application.Dialogs(xlDialogPageSetup).Show
         End Sub
   						

2. With the Visual Basic module active, click Menu Editor on the Tools menu.
3. In the Menu Bars box, click the name of the menu bar to which you want to add the menu. In the Menu Bars list, click Worksheet.
4. In the Menus box, click the name of the menu to the left of which you want to insert the new menu. To add the menu to the right end of the menu bar, click End Of Menu Bar. Click "&Help" in the Menus box. Click Insert.
5. In the Caption box, type the name you want for the new menu or submenu. In the Caption box, type &My Menu.
   
   NOTE: Type an ampersand (&) before the character you want to use as the access key. To create a separator bar, type a hyphen (-).
6. In the Menu Items box, click the name of the menu item above which you want to insert the new menu item. To add the new item to the bottom of the menu, click End Of Menu. in the Menu Items box, Click on End Of Menu, and click Insert.
7. In the Caption box, type the name you want for the new menu item. Type &Page Setup.
   
   NOTE: Type an ampersand (&) before the character you want to use as the access key. To create a separator bar, type a hyphen (-). In this example, when the menu is displayed, the (t) after the (&) in "Set" will be underlined.
8. To add a submenu, in the Menu Items box, click the name of the menu item that you want the submenu to be linked to. In the Submenu Items box, click the name of the submenu item above which you want to insert the new submenu item. To add the new item to the bottom of the menu, click End Of Menu. In the Submenu Items box, click End Of Menu, and click Insert.
9. In the Caption box, type the name you want for the new submenu item. Type &Show Page Setup.

To specify the macro you want to run when the user clicks the submenu item, follow these steps:

1. Enter the name of the procedure in the Macro box. Click the ShowPageSetup macro in the Macro list.
2. Accept the changes made. Click OK.

To use the new menu, menu item, and submenu item, click Page Setup on the My Menu menu, then click the Show Page Setup submenu.

For more information about using the Menu Editor, click the Index tab in Microsoft Excel 7.0 Help, type the following text and then double-click the selected text to go to the "Customizing menus" topic.

"Visual Basic User's Guide," version 5.0, pages 241-264