XL: How to Use the Menu Editor

ID: Q141579

The information in this article applies to:

SUMMARY

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.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/

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" (without the quotation marks).

   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" (without the quotation marks).

   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" (without the quotation marks).

   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" (without the quotation marks).
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.

REFERENCES

For more information about using the Menu Editor, click the Index tab in Microsoft Excel 7.0 Help, type the following text 

   menu editor
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

Additional query words: 5.00 7.00 XL5 XL7 

Keywords          : kbcode kbprg PgmHowto 
Version           : WINDOWS:5.0,7.0; MACINTOSH:5.0
Platform          : MACINTOSH WINDOWS
Issue type        : kbhowto

Last Reviewed: May 17, 1999