Excel 2016 Power Programming with VBA

18 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. 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.

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 = 1)
• msoBarTypePopUp: A shortcut menu (Type = 2)

Even though toolbars and menu bars aren’t used in Excel 2007 and later, these UI elements are still included in the object model for compatibility with older applications. However, attempting to display a CommandBar of Type 0 or 1 has no effect in Excel versions after Excel 2003. In Excel 2003, for example, the following statement displays the ...