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.
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
Toolbar Menu Bar Shortcut Menu
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?