Excel 2013 Power Programming with VBA

Chapter 21: Working with Shortcut Menus

In This Chapter

• Identifying shortcut menus

• Customizing the shortcut menus

• Disabling shortcut menus

• Using events with shortcut menus

• Creating a new shortcut menu

CommandBar Overview

A CommandBar object is used for three Excel user interface elements:

• Custom toolbars

• Custom menus

• Customs shortcut (right-click) menus

Beginning with Excel 2007, the CommandBar object is in an odd position. If you write VBA code to customize a menu or a toolbar, Excel intercepts that code and ignores many of your commands. As I describe in Chapter 20, menu and toolbar customizations performed with the CommandBar object appear in the Add-Ins⇒Menu Commands group or the Add-Ins⇒Custom Toolbars group. So, for all practical purposes, the CommandBar object in Excel is now limited to shortcut menu operations.

The new single-document interface for Excel 2013 affects custom shortcut menus. VBA code that creates custom shortcut menus may not work correctly in Excel 2013. I discuss this topic later in this chapter (see “What's different in Excel 2013”).

In this section, I provide some background information about CommandBars.

CommandBar types

Excel supports three types of CommandBars, differentiated by their Type property. The Type property can be any of these three values:

• msoBarTypeNormal: A toolbar (Type = 0)

• msoBarTypeMenuBar: A menu bar (Type = ...