O'Reilly logo

Excel® 2010 Power Programming with VBA by John Walkenbach

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Chapter 23: Working with Shortcut Menus

IN THIS CHAPTER

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required