Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)

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

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:

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:

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:

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.

Related

Категории