To add a menu bar to a workbookIf you want to design a set of drop-down menus that differs significantly from what is available on the built-in menu bars, you may need to create a new menu bar. You can accomplish this using the Menu Editor or Visual Basic procedures.
The Menu Editor provides a convenient way to add a custom menu bar to the active workbook.
To add a menu bar to a workbook
1. While a Visual Basic module is active, click Menu Editor on the Tools menu.
2. In the Menu Bars box, click the arrow to display the drop-down list, and then click the name of any menu bar.
This step cancels the selection of items in the Menus, Menu Items, and Submenu Items boxes. If you omit this step, the text you type in the Caption box becomes the first menu item on the first menu that's selected in the Menus box.
3. Click Insert.
4. In the Caption box, either type a name for the new menu bar or accept the default name.
The new menu bar is added to the list in the Menu Bars box, below the final item. The default list of built-in menu bars is shown in the following table. The custom menu bars you create are listed in the order they're created. You can use up to 15 custom menu bars at one time.
|
Menu bar |
Description | |
|
Worksheet |
The menu bar that appears when a worksheet is active | |
|
Chart |
The menu bar that appears when a chart is active | |
|
No Documents Open |
The menu bar that appears when no workbooks are open | |
|
Visual Basic Module |
The menu bar that appears when a Visual Basic module is active | |
|
Shortcut Menus 1 |
A group of shortcut menus that appears when the user clicks a toolbar, toolbar button, cell, column or row selection, workbook tab, window title bar, or the desktop with the right mouse button | |
|
Shortcut Menus 2 |
A group of shortcut menus that appears when the user clicks a drawing object, button graphic object, or text box graphic object with the right mouse button | |
|
Shortcut Menus 3 |
A group of shortcut menus that appears when the user clicks a chart series, text, plot area, axis, gridline, floor or arrow, legend, or the entire chart with the right mouse button | |
Note
A shortcut menu is a type of menu, and each shortcut menu is represented by a Menu object in Visual Basic. Although shortcut menus are shown grouped into menu bars in the Menu Editor, a shortcut menu group isn't represented by a MenuBar object in Visual Basic.
Use the Add method of the MenuBars collection to create a new menu bar; specifying the name of the new menu bar is optional.
MenuBars.Add "myWorksheetMenubar"
The MenuBars collection contains all the menu bars available in Microsoft Excel. The shortcut menu groups that appear in the Menu Editor cannot be accessed with the MenuBars method, as they aren't true menu bars. Use the ShortcutMenus method to access a shortcut menu, as described later in this chapter.