CommandBar Overview

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

• Custom toolbars

• Custom menus

• Customs shortcut (right-click) menus

In 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-Ins Menu Commands or the Add-Ins Custom Toolbars group . So, for all practical purposes, the CommandBar object in Excel 2007 is 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, 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 2007. In Excel 2003, for example, the following statement displays the Standard toolbar.

 CommandBars("Standard").Visible = True 

In Excel 2007, that statement is ignored.

This chapter focuses exclusively on Type 2 CommandBars (shortcut menus).

Listing shortcut menus

Excel 2007 has 65 shortcut menus. How do I know that? I ran the ShowShortcutMenuNames procedure that follows , which loops through all CommandBars. If the Type property is msoBarTypePopUp (a built-in constant that has a value of 2), it displays the CommandBar's index and name in a worksheet.

 Sub ShowShortcutMenuNames()     Dim Row As Long     Dim cbar As CommandBar     Row = 1     For Each cbar In CommandBars         If cbar.Type = msoBarTypePopUp Then             Cells(Row, 1) = cbar.Index             Cells(Row, 2) = cbar.Name             Row = Row + 1         End If     Next cbar End Sub 

Figure 23-1 shows part of the output from this procedure. The shortcut menu index values range from 21 to 145. Also, notice that not all the names are unique. For example, CommandBar 36 and CommandBar 39 both have a Name of Cell . This is because right-clicking a cell gives a different shortcut menu when the worksheet is in Page Break Preview mode.

Figure 23-1: A simple macro generates a list of all shortcut menus.

CD  

This example is available on the companion CD-ROM. The filename is  show shortcut menu names.xlsm

Referring to CommandBars

You can reference a particular CommandBar object by its Index or by its Name property. For example, the expressions that follow both refer to the shortcut menu that displays when you right-click the Excel desktop (the area that's visible when no documents are open ):

 Application.CommandBars (45) Application.CommandBars("Desktop") 

The CommandBars collection is a member of the Application object. When you reference this collection in a regular VBA module or in a module for a sheet, you can omit the reference to the Application object. For example, the following statement (contained in a standard VBA module) displays the name of the object in the CommandBars collection that has an index of 45:

 MsgBox CommandBars(45).Name 

When you reference the CommandBars collection from a code module for a ThisWorkbook object, you must precede it with a reference to the Application object, like this:

 MsgBox Application.CommandBars(45).Name 

Note  

Unfortunately, the Index numbers have not always remained constant across the different Excel versions.

Referring to controls in a CommandBar

A CommandBar object contains Control objects, which are buttons or menus. You can refer to a control by its Index property or by its Caption property. Here's a simple procedure that displays the caption of the first menu item on the Cell shortcut menu:

 Sub ShowCaption()     MsgBox Application.CommandBars("Cell"). _        Controls(1).Caption End Sub 

The following procedure displays the Caption property for each control in the shortcut menu that appears when you right-click a sheet tab (that shortcut menu is named Ply ):

 Sub ShowCaptions()     Dim txt As String     Dim ctl As CommandBarControl     For Each ctl In CommandBars("Ply").Controls         txt = txt & ctl.Caption & vbNewLine     Next ctl     MsgBox txt End Sub 

When you execute this procedure, you see the message box shown in Figure 23-2. The ampersand is used to indicate the underlined letter in the text - the keystroke that will execute the menu item.

Figure 23-2: Displaying the Caption property for controls.

In some cases, Control objects on a shortcut menu contain other Control objects. For example, the Filter control on the Cell right-click menu contains other controls. The Filter control is a submenu, and the additional items are submenu items.

Finding a Control

If you are writing code that will be used by a different language version of Excel, avoid using the Caption property to access a particular shortcut menu item. The Caption property is language-specific, so your code will fail if the user has a different language version of Excel.

Instead, use the FindControl method in conjunction with the ID of the control (which is language-independent). For example, assume that you want to disable the Rename menu on the shortcut menu that appears when you right-click a sheet tab. If your workbook will be used only by people who have the English version of Excel, this statement will do the job:

 CommandBars("Ply").Controls("Rename").Enabled = False 

To ensure that the command will work with non-English versions, you need to know the ID of the control. The following statement will tell you that the ID is 889:

 MsgBox CommandBars("Ply").Controls("Rename").ID 

Then, to disable that control, use this statement:

 CommandBars.FindControl(ID:=889).Enabled = False 

The CommandBar names are not internationalized, so a reference to CommandBars("Desktop") will always work.

 

The statement that follows displays the first submenu item in the Filter submenu:

 MsgBox CommandBars("Cell").Controls("Filter").Controls(1).Caption 

Properties of CommandBar controls

CommandBar controls have a number of properties that determine how the controls look and work. This list contains some of the more useful properties for CommandBar controls:

• Caption : The text displayed for the control. If the control shows only an image, the Caption appears when you move the mouse over the control.

• ID: A unique numeric identifier for the control.

• FaceID : A number that represents a graphic image displayed next to the control's text.

• Type : A value that determines whether a control is a button ( msoControlButton ) or a submenu ( msoControlPopup ).

• Picture: A graphics image displayed next to the control's text.

• BeginGroup : True if a separator bar appears before the control.

• OnAction : The name of a VBA macro that executes when the user clicks the control.

• BuiltIn : True if the control is an Excel built-in control.

• Enabled : True if the control can be clicked.

• Visible : True if the control is visible. Many of the shortcut menus contains hidden controls.

• ToolTipText : Text that appears when the user moves the mouse pointer over the control. (Not applicable for shortcut menus.)

Displaying all shortcut menu items

The ShowShortcutMenuItems procedure that follows creates a table that lists all of the first-level controls on every shortcut menu. For each control, the table includes the shortcut menu's Index and Name , plus the ID , Caption , Type , Enabled , and Visible property values.

 Sub ShowShortcutMenuItems()     Dim Row As Long     Dim Cbar As CommandBar     Dim ctl As CommandBarControl     Range("A1:G1") = Array("Index", "Name", "ID", "Caption", _       "Type", "Enabled", "Visible")     Row = 2     Application.ScreenUpdating = False     For Each Cbar In Application.CommandBars       If Cbar.Type = 2 Then         For Each ctl In Cbar.Controls             Cells(Row, 1) = Cbar.Index             Cells(Row, 2) = Cbar.Name             Cells(Row, 3) = ctl.ID             Cells(Row, 4) = ctl.Caption             If ctl.Type = 1 Then                 Cells(Row, 5) = "Button"             Else                 Cells(Row, 5) = "Submenu"             End If             Cells(Row, 6) = ctl.Enabled             Cells(Row, 7) = ctl.Visible            Row = Row + 1        Next ctl      End If     Next Cbar End Sub 

Figure 23-3 shows a portion of the output.

Figure 23-3: Listing the items in all shortcut menus.

If you run the ShowShortcutMenuItems macro, you see that many of the shortcut menus contain hidden or disabled controls. These hidden or disabled menu items represent items that are not available because of the current context. For example, the Desktop shortcut menu (Index 45) contains the following menu items:

• &New

• &Open

• Save &Workspace

• &Calculate Now

• F&ull Screen

The Full Screen menu item is normally hidden - unless Excel is in full screen mode. In such a case, the menu item is made visible and its caption is changed to &Close Full Screen .

CD-ROM  

This example, named  show shortcut menu items.xlsm , is available on the companion CD-ROM.