Writing Excel Macros with VBA, 2nd Edition

Chapter 12. Custom Menus and Toolbars

In this chapter, we discuss methods for programmatically controlling menus and toolbars. Even though the subject of menus and toolbars is fairly straightforward, it can seem very confusing, especially since the documentation is less helpful than it might be.

Menus and Toolbars: An Overview

Actually, Excel’s menu and toolbar objects do not belong to the Excel object model. The menus and toolbars throughout the Microsoft Office application suite belong to the Office object model. The portion of the Office object model that relates to menus and toolbars is shown in Figure 12-1.

Figure 12-1. The menu and toolbar portion of the Office object model

Note that this model is actually quite small, containing only two objects and their corresponding collections:

• CommandBar objects and the CommandBars collection

• CommandBarControl objects and the CommandBarControls collection

Menu Terminology

To help set the notation, Figure 12-2 shows the components of the Office menu structure (this happens to be a Word menu, but no matter).

Figure 12-2. An Office menu

The CommandBar Object

Toolbars, menu bars, menus, submenus, and shortcut menus are all CommandBar objects. (A shortcut menu is a menu that pops up in response to a right mouse click.) Thus, every item pictured ...