O'Reilly logo

Excel 2013 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 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.

newfeature.eps

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

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