Creating Custom Menus
A companion file is available: Click here to download
Excel 97 through Excel 2003 use CommandBars for their menus. Workbook-specific menus must be created programmatically using VBA code. This tip describes a relatively simple way to create a custom menu (on the Worksheet Menu Bar) that appears when a particular workbook is opened, and is deleted when the workbook is closed.
Note: Although this technique works in Excel 2007, the results are not what you would expect. All custom CommandBars are displayed in the Add-Ins tab.
The example file contains all of the VBA code that you need to create your own custom menus. In most cases, you will not need to make any changes to the VBA code - simply customize the MenuSheet worksheet.
Please note that this technique will not work if you need to add a menu item to an existing menu.
How it works
This technique uses a table, which is stored in a worksheet. The figure below shows such a table. To create a custom menu, simply modify the data in the table.
This table contains five columns:
- Level: The "level" of the particular item. Valid values are 1, 2, and 3. A level of 1 is for a menu; 2 is for a menu item; and 3 is for a submenu item. Normally, you'll have one level 1 item, with level 2 items below it. A level 2 item may or may not have level 3 (submenus) items.
- Caption: The text that appears in the menu, menu item, or submenu. Use an ampersand (&) to specify a character that will be underlined.
- Position/Macro: For level 1 items, this should be an integer that represents the position in the menubar. For level 2 or level 3 items, this will be the macro that is executed when the item is selected. If a level 2 item has one or more level 3 items, the level 2 item may not have a macro associated with it.
- Divider: True if a "divider" should be placed before the menu item or submenu item.
- FaceID: Optional. A code number that represents the built-in graphic images that are displayed next to an item.
An example menu
The figure below shows the menu that is created using the table above.
Using this technique
To use this technique in your workbook or add-in, follow these general steps:
- Download menumakr.xls (see link, above). This file contains the VBA code, plus a worksheet name MenuSheet.
- Copy all of the code in Module1 to a module in your project.
- Add subroutines like these to the code module for the ThisWorkbook object:
- The Workbook_Open subroutine is executed when the workbook is opened; the Workbook_BeforeClose subroutine is executed before the workbook is closed.
- Insert a new worksheet and name it MenuSheet. Better yet, copy the MenuSheet from the menumakr.xls file.
- Customize the MenuSheet to correspond to your custom menu.
- There is no error handling, so it's up to you to make sure that everything works.
Private Sub Workbook_Open() Call CreateMenu End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call DeleteMenu End Sub
Excel has a long history, and it continues to evolve and change. Consequently, the tips provided here do not necessarily apply to all versions of Excel.
In particular, the user interface for Excel 2007 (and later), is vastly different from its predecessors. Therefore, the menu commands listed in older tips, will not correspond to the Excel 2007 (and later) user interface.
Browse Tips by Category
Search for Tips
Needs tips? Here are two books, with nothing but tips:
Contains more than 200 useful tips and tricks for Excel 2007 | Other Excel 2007 books | Amazon link: John Walkenbach's Favorite Excel 2007 Tips & Tricks