Using the Command Bar Object

Vertex42 The Excel Nexus

Professional Excel Templates

Get Instant Access

CommandBars is a collection of objects that represents all the toolbars in the active application. Each object in this collection is called CommandBar. The term "CommandBar" is used to refer to a menu bar, a shortcut menu, or a toolbar.

Because the CommandBar object can refer to various tools (toolbar, menu bar, shortcut menu), this object comes with a special Type property that can be used to return the specific type of the toolbar as shown in Table

Table 12-1: Types of CommandBar objects in the CommandBars collection

1. Open a new workbook and save it as Chap12.xls.

2. Switch to the Visual Basic Editor screen.

3. Highlight the current VBA Chap12.xls project in the Project Explorer window and rename it CustomTools.

4. Add a new module to the CustomTools project.

5. Enter the MyToolBars procedure, as shown below:

Sub MyToolBars( )

Dim bar As CommandBar Dim r As Integer r = 1

ActiveSheet.Range("A1").Formjla = "List of Toolbars" For Each bar In CommandBars

If bar.Type = msoBarTypeNormal Then With Worksheets("Sheet1").Range("A1") .Offset(r, 0) = bar.Name .Offset(r, l) = bar.Index End With r = r + 1 End If

Type of Object

Index Constant

Toolbar Menu Bar Shortcut Menu

0 msoBarTypeNormal

1 msoBarTypeMenuBar

2 msoBarTypePopup


Set bar = Nothing End Sub

The above procedure searches the CommandBars collection and selects only those tools with the Type Property equal to msoBarTypeNormal. If the specific element of the CommandBars collection is a toolbar, Visual Basic enters its name in the first column of the active worksheet. Column B stores the object's index.

1. Modify the above procedure so that it writes to the spreadsheet the names of all the objects in the CommandBars collection (toolbars, menu bars, shortcut menus). Use Table 12-1 as a reference.

To refer to a specific toolbar in a CommandBars collection, you can use its name or index number.

1. Enter the following statement in the Immediate window: ?CommandBars(1).Name

When you press Enter, Visual Basic returns the name of the first element of the CommandBars collection.

2. Enter the following statement in the Immediate window: ?CommandBars("Circular Reference").Type

Visual Basic returns zero (0). This is the index number of a toolbar (see Table 12-1).

3. To calculate the total number of tools available in the CommandBars collection, use the Count property. Try the following statement in the Immediate window:


Was this article helpful?

0 0

Post a comment