XL: How to Use the Menu Editor

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows NT, version 5.0
• Microsoft Excel for Windows 95, versions 7.0, 7.0a

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 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 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/default.asp

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.

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).

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.

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

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