Understanding Excel Menus with VBA

In This Chapter

Knowing menu terminology
Understanding the types of menu modifications you can make 
Understanding the object model Modifying menus with VBA
You may not realize it, but you can change almost every aspect of Excel’s menus. Typical Excel users get by just fine with the standard menus. Because you’re reading this topic, however, you’re probably not the typical Excel user. In this chapter I describe how to make changes to the Excel menu system.
Most of the Excel applications you develop get along just fine with the standard menu system. In some cases, however, you may want to add a new menu to make it easier to run your VBA macros. In other cases, you may want to remove some menu items to prevent users from accessing certain features. If these sorts of changes seem useful, you should read this chapter. Otherwise, you can safely skip it until the need arises.

Defining Menu Lingo

Before I get too far into this section, I need to discuss terminology. At first, menu terminology confuses people because many of the terms are similar. The following list describes official Excel menu terminology, which I use throughout this chapter and the rest of the topic:
Menu bar: The row of words that appears directly below the application’s title bar. Excel has two menu bars that appear automatically, depending on the context. The menu bar displayed when a worksheet is active differs from the menu bar displayed when a chart sheet is active.
Menu: A single, top-level element of a menu bar. For example, both of Excel’s menu bars have a menu called File.
Menu item: An element that appears in the drop-down list when you select a menu. For example, the first menu item under the File menu is New. Menu items also appear in submenus and shortcut menus.
Separator bar: A horizontal line that appears between two menu items. The separator bar groups similar menu items.
Submenu: A menu that is under some menus. For example, the Edit menu has a submenu called Clear.
Submenu item: A menu item that appears in the list when you select a submenu. For example, the EditOClear submenu contains the following submenu items: All, Formats, Contents, and Comments.
Shortcut menu: The floating list of menu items that appears when you right-click a selection or an object. In Excel, you can right-click just about anything and get a shortcut menu.
Enabled: A menu item that can be used. If a menu item isn’t enabled, its text appears grayed out and it can’t be used.
Checked: A menu item can display a graphical box that is checked or unchecked. The ViewOStatus Bar menu item is an example.


How Excel Handles Menus

Excel provides you with two ways to change the menu system:

Use the View Toolbars Customize command. This displays the Customize dialog box, which lets you change menus (and toolbars).

Write VBA code to modify the menu system

When you close Excel, it saves any changes that you’ve made to the menu system, and these changes appear the next time you open Excel. The information about menu modifications is stored in a file with an XLB extension. In most cases, you won’t want your menu modifications to be saved between sessions. Generally, you need to write VBA code to change the menus while a particular workbook is open and then change them back when the workbook closes.

Customizing Menus Directly

Because a menu bar is actually a toolbar in disguise, you can modify a menu bar using the View Toolbars Customize command. To demonstrate how easy it is to change the Excel menu, try this:
If you’ve already modified your menu, don’t perform the following steps. Doing so involves resetting your menu bar, which wipes out all of your menu modifications.
1. Choose View Toolbars Customize.
2. Click the Help menu (in the menu bar) and drag it away.
3. Click Close to close the Customize dialog box.
You’ve just wiped out your Help menu (see Figure 20-1). Exit Excel and then restart it. Although you may expect the menu bar to be restored when you restart Excel, it’s not! The Help menu is still missing. Follow these steps to get things back to normal:
1. Choose View Toolbars Customize.
The Customize dialog box appears.
2. Click the Toolbars tab.
3. Select the Worksheet Menu Bar item in the Toolbars list.
4. Click Reset to restore the Worksheet menu bar to its default state.
The worksheet menu bar after zapping the Help menu.
Figure 20-1:
The worksheet menu bar after zapping the Help menu.
I think Microsoft made it far too easy to mess up your menus. My advice? Don’t use the View Toolbars Customize command to change menus. In almost every case, you want your menu modifications to be in effect only when a particular workbook is open. To do that, you need to use VBA to make your menu changes — which is the topic of this chapter.
Refer to Chapter 19 for details regarding the Customize dialog box. The procedures that I describe for toolbars also work for menu bars.

Looking Out for the CommandBar Object

For an introduction to the CommandBar object, refer to Chapter 19. There I explain that a menu bar is one of three types of CommandBars.

Referring to CommandBars

Because this chapter deals with menus, you’re interested in the two CommandBar objects that are built-in menu bars. The Worksheet Menu Bar is the first object in the CommandBars collection, so you can refer to it as one of the following:
tmp59-23_thumb
or
tmp59-24_thumb
Similarly, you can refer to the Chart Menu Bar object using either one of these:
tmp59-25_thumbtmp59-26_thumbtmp59-27_thumb
In Chapter 19, I advise against using the index numbers when referring to toolbars, because the toolbar index numbers change from version to version. That warning doesn’t apply to the two menu bars. The two menu bars always have index numbers of 1 and 2.

Referring to Controls in a CommandBar

A CommandBar object contains Control objects, and these Control objects can contain other Control objects. When you choose File Open, you’re actually manipulating a Control (a menu item with a Caption of Open) that’s contained in another Control (a menu with a Caption of File) that’s contained in a CommandBar (the Worksheet menu bar).
Confused? Here’s an example that may help clear things up. The following macro displays the Caption property for the first Control contained in the first Control of the first CommandBar object. When you execute this procedure, you see the message box shown in Figure 20-2.
tmp59-28_thumbDisplaying the Caption property for a control.
Figure 20-2:
Displaying the Caption property for a control.
Rather than use index numbers, you can also use the captions, which makes things a bit clearer. The next procedure has the same result as the preceding procedure:
tmp59-30_thumb
If you refer to a control by using its caption, the caption must exactly match the text displayed in the menu. In the preceding procedure, I had to use ellipses (three dots) after the word New because that’s how it appears in the menu. You can mix and match index numbers with captions, if you like.
You might find the next procedure instructive. It loops through all the Controls (menus) in the Worksheet menu bar. For each of these menus, it loops through the Controls (menu items) within the menu. For each of these menu items, it loops through the Controls (submenu items). The result is a listing of all menus, menu items, and submenu items in the Worksheet menu bar.
tmp59-31_thumbtmp59-32_thumb
Notice that I use On Error Resume Next to ignore the error that occurs if a menu item doesn’t have any submenu items. Figure 20-3 shows part of the output from this procedure. This code is available on this topic’s Web site.
List all menus, menu items, and submenu items in the Worksheet menu bar.
Figure 20-3:
List all menus, menu items, and submenu items in the Worksheet menu bar.

Properties of CommandBar Controls

CommandBar controls have a number of properties that determine how they look and work. This list contains some of the more useful CommandBar control properties:
Caption: The text displayed for the control.
FaceID: A number that represents a graphics image displayed next to the control’s text.
BeginGroup: True if a separator bar appears before the control.
OnAction: The name of a VBA macro to execute when the user clicks the control.
BuiltIn: True if the control is an Excel built-in control. Enabled: True if the control can be clicked.
ToolTipText: Text that appears when the user moves the mouse pointer over the control.

Placing your menu code

Most of the time you want your menu changes to be in effect only when a particular workbook is open. Therefore, you need VBA code to modify the menu when the workbook is opened and more VBA code to return the menus to normal when the workbook is closed. A good place for your menu-manipulating code: in the code window for the This Workbook object. More specifically, you use the following two event-handler procedures, which you can find out more about in Chapter 11:
Sub Workbook_Open( )
Sub Workbook_BeforeClose(Cancel As Boolean)
If you would like to set things up so that a particular menu modification appears only when a specific workbook is active, store your menu manipulation procedures in the Workbook_Activate and Workbook_Deactivate procedures. Refer to Chapter 19 for an example that uses toolbars.

Would You Like to See Our Menu Examples?

In this section I present some practical examples of VBA code that manipulates Excel’s menus. Adapt these examples for your own use.

Creating a menu

When you create a menu, you add a new word to a menu bar. The control that you add is of the Popup type because the menu always contains menu items.
The following procedure adds a new menu (Budgeting) between the Window menu and the Help menu on the Worksheet menu bar:
tmp59-34_thumb
Notice that this procedure creates an essentially worthless menu — it has no menu items. See the next section, “Adding a menu item,” for an example of adding a menu item to a menu. Figure 20-4 shows the Worksheet menu bar after executing this procedure.
This example is available on this topic’s Web site.

Adding the menu is a two-step process:

1. Create an object variable that refers to the new Control.
In this case, the object variable is named NewMenu, and its type is CommandBarPopup.
2. Adjust the new Control’s properties.
In this case, I changed only one property: Caption.
A new menu, called Budgeting, was added to this menu bar.
Figure 20-4:
A new menu, called Budgeting, was added to this menu bar.
The preceding procedure uses the Add method, with three named arguments:
Type: Identifies the type of control. (msoControlPopup is a built-in constant.)
Before: Identifies the position on the menu bar. I determined the position of the Help menu and assigned it to the variable HelpIndex.
Temporary: This argument is True, so the menu change is not permanent. In other words, the Budgeting menu doesn’t appear when you restart Excel.

Adding a menu item

The example in the preceding section demonstrates how to create a new menu. The following example adds a menu item to the Excel Format menu. This menu item, when clicked, executes a macro named Toggle Word Wrap. The Toggle Word Wrap procedure changes the WrapText property of the selected cells.
After creating the menu item, I change the Caption, OnAction, and BeginGroup properties. Setting BeginGroup to True displays a separator bar before the new menu item. Figure 20-5 shows the modified Format menu.
tmp59-36_thumbThe Format menu has a new menu item: Toggle Word Wrap.
Figure 20-5:
The Format menu has a new menu item: Toggle Word Wrap.
The Toggle Word Wrap procedure, available on this topic’s Web site, is shown next:
tmp59-38_thumb

Deleting a menu

In some cases, you may want to delete one or more Excel built-in menus while a particular workbook is open, or delete a custom menu that you added.

The following statements delete the Help menu for the Worksheet menu bar and the Chart menu bar:

tmp59-39_thumb
You get an error message if the Help menu doesn’t exist. Therefore, you might want to precede the statements with the following statement, which causes Excel to ignore any errors:
tmp59-40_thumb

You can restore in one of two ways built-in menu items that you deleted:

Reset the entire menu bar.
Use the Add method to add the built-in menu and then add all the menu items. You may want to record your actions while you use the Customize dialog box to restore a menu.

The following statements reset the Worksheet menu bar and the Chart menu bar:

tmp59-41_thumb
Resetting a menu bar destroys any customization you may have performed. For example, if you added a new menu item to the Tools menu, that menu item is removed when the menu bar is reset.

Deleting a menu item

Use the Delete method to delete custom or built-in menu items. The following statement deletes the Exit menu item from the File menu on the Worksheet menu bar:
tmp59-42_thumb
To get this menu item back, use the Add method:
tmp59-43_thumb
Notice that I have to supply the Id for the Control that I add. The Id for the Exit menu item is 752. How did I figure that out? Before deleting the File menu, I typed the following statement in the Immediate window of the VBE (followed by pressing Enter to see the result):
tmp59-44_thumb

Changing menu captions

You can change the text displayed (the Caption) for both custom and built-in menus and menu items. You do so by changing the Caption property. This example changes the Help menu in the Worksheet menu bar so that it displays Assistance:
tmp59-45_thumb
The following example, available on this topic’s Web site, changes the text of all the menus, menu items, and submenu items to uppercase letters — probably not something you’d want to do, but it does give Excel a new look:
tmp59-46_thumb
Figure 20-6 shows how the menus look after you execute this routine. To return things to normal, see the “So you messed up your menus, eh?” sidebar in this chapter.

So you messed up your menus, eh?

As you work your way through this chapter, I hope you try out various examples and write your own code to change the Excel menus. In the process, your menus might get messed up. Mine did as I was writing this chapter. Don’t fret. It’s easy to restore the menu bar to its factory-fresh, out-of-the box state:
1. Choose View Toolbars Customize.
The Customize dialog box appears.
2. Click the Toolbars tab.
3. Select the Worksheet Menu Bar item (or the Chart Menu Bar item if you’ve messed up that menu).
4. Click Reset.
Or you can just execute this VBA statement to reset the Worksheet Menu Bar:

Application.CommandBars(1).Reset

Using either method, the menu bar is restored to its default state.
Excel's menus look like this after being converted to uppercase.
Figure 20-6:
Excel’s menus look like this after being converted to uppercase.

Adding a menu item to the Tools menu

A common task is to add a new item to the Tools menu (or any other menu, for that matter). The best approach is to write your own VBA code to do so. Or you can simply use the code that follows. No charge.
The two macros are stored in the code window for the This Workbook object. The procedures are event handlers that respond to the Workbook Open event and the Workbook BeforeClose event. They include some simple error-handling
code to handle the case in which there is no Tools menu (or if it has a different name)
tmp6F-1
The example adds a new menu item (Run Budget Macro) to the Tools menu when the workbook is opened. This menu item, when clicked, runs a macro named UpdateBudget. When the workbook is closed, the menu item is removed from the Tools menu.
To use this code in your own application, enter the code into the code window for the This Workbook object; then change the values for the MenuItemName and MenuItemMacro constants.
This example is available on this topic’s Web site.

Working with Shortcut Menus

You can’t use the Customize dialog box to remove or modify shortcut menus. The only way to customize shortcut menus is to use VBA.
A shortcut menu appears when you right-click an object. Excel has lots of shortcut menus. You can pretty much right-click anything and get a shortcut menu. To work with a shortcut menu, you need to know the shortcut menu’s Caption or Index. This information isn’t available in the Help system, but you can use the following procedure to generate a list of all shortcut menus and the Index and Caption for each:
tmp59-49_thumb
This example, available on this topic’s Web site, produces something like that shown in Figure 20-7.
Be careful when you modify shortcut menus. Like normal menus, changes to shortcut menus are saved between sessions. Therefore, you probably want to reset the shortcut menus when your application ends.
A listing of all shortcut menus, plus the menu items in each.
Figure 20-7:
A listing of all shortcut menus, plus the menu items in each.

Adding menu items to a shortcut menu

Adding a menu item to a shortcut menu works just like adding a menu item to a regular menu. You need to know the Index or the Caption for the shortcut menu. See the preceding section to find out how to get a list of all shortcut menus.
The following example demonstrates how to add a menu item to the shortcut menu that appears when you right-click a cell. The Caption for this shortcut menu is Cell. This menu item is added to the end of the shortcut menu.
tmp59-51_thumb
Selecting the new menu item executes the Toggle Word Wrap procedure.

Deleting menu items from a shortcut menu

The following routine removes the Hide menu item from two shortcut menus: the menus that appear when you right-click a row header or a column header.
tmp59-52_thumb

To restore these shortcut menus, use this procedure:

tmp59-53_thumb

Disabling shortcut menus

You may want to disable one or more shortcut menus while your application is running. For example, you may not want the user to access the commands by right-clicking a cell. If you want to disable all shortcut menus, use the following procedure, also available on this topic’s Web site:
tmp59-54_thumb
Disabling the shortcut menus remains between sessions. Therefore, you probably want to restore the shortcut menus before closing Excel. To restore the shortcut menus, modify the preceding procedure by setting Enabled to True.

Finding Out More

This chapter introduces the concept of menu modifications made with VBA and presents several examples that you can adapt to your own needs. A good way to find out more about using VBA to modify menus is to record your actions while you make menu changes using the View Toolbars Customize command. And, of course, the online Help system contains all the details you need.

Next post:

Previous post: