- 

 

    
Home
Back

 
A technique that makes it very easy to create a custom menu for a workbook or add-in.
VBA programming not required!


Download an example

You can download an example workbook that demonstrates this technique.

          Download menumaker.zip (14k)

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. Follow this link for more information about determining
    FaceID code numbers.


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:

  1. Download menumaker.zip. This file contains the VBA code, plus a worksheet name
    MenuSheet.

  2. Copy all of the code in Module1 to a module in your project.

  3. Add subroutines like these to the code module for the ThisWorkbook object:

    Private Sub Workbook_Open()
        Call CreateMenu
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        Call DeleteMenu
    End Sub
     

  4. The Workbook_Open subroutine is executed when the workbook is opened; the
    Workbook_BeforeClose subroutine is executed before the workbook is closed.

  5. Insert a new worksheet and name it MenuSheet. Better yet, copy the MenuSheet
    from the menumaker.xla file.

  6. Customize the MenuSheet to correspond to your custom menu.

  7. There is no error handling, so it's up to you to make sure that everything works.