Chapter 23: Working with Shortcut Menus


Identifying shortcut menus

Customizing the shortcut menus

Disabling shortcut menus

Using events in conjunction with shortcut menus

Creating a completely 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 a rather 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 22, menu and toolbar customizations performed with the CommandBar object appear in the Add-InsMenu Commands or the Add-InsCustom 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 2010, 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 2010. In Excel 2003, for example, ...

Get Excel® 2010 Power Programming with VBA now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.