Databases Reference
In-Depth Information
Appendix B: Excel Developer Tip
A very special thank you to John Walkenbach for allowing reprint and use of his work. Please see:
http://j-walk.com/ss and http://j-walk.com/ss/excel/tips/tip53.htm.
B.1
CREATING CUSTOM MENUS
If you've moved up to Excel 97 (or later) from a previous version, you may have noticed that
the former menu editor is no longer available. The menu editor made it easy to create a custom
menu that was stored with a particular workbook. Opening the workbook added the menu, and
closing the workbook removed the menu.
The menu editor was removed because Excel 97 and later versions use CommandBars — a
new (and much move superior) method of dealing with 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.
To create a custom menu, simply modify the data in the table which consists of five columns.
B.2
DOWNLOAD AN EXAMPLE
You can download an example workbook that demonstrates this technique.
•D wnload menumakr.xls (61K)
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.
B.3
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.
•
The text that appears in the menu, menu item, or submenu. Use an
ampersand (&) to specify a character that will be underlined.
Caption:
•
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.
405
