Listing all Command Bar objects

If you're curious about the objects in the CommandBars collection, the following procedure should be enlightening. Executing this procedure generates a list (as shown in Figure 22-6) of all CommandBar objects in the CommandBars collection. For Excel 2003, it lists a total of 126 built-in command bars, plus any custom menu bars or toolbars. For each command bar, the procedure lists its Index, Name, and Type property settings (displayed as Toolbar, Menu Bar, or Shortcut) as well as whether it's a built-in command bar.

Figure 22-6: VBA code produced this list of all CommandBar objects.

Sub ShowCommandBarNames() Dim Row As Integer Dim cbar As CommandBar

Cells.Clear Row = 1

For Each cbar In CommandBars Cells(Row, 1) = cbar.Index Cells(Row, 2) = cbar.Name Select Case cbar.Type

Case msoBarTypeNormal

Cells(Row, 3) = "Toolbar" Case msoBarTypeMenuBar

Cells(Row, 3) = "Menu Bar" Case msoBarTypePopUp

Cells(Row, 3) = "Shortcut" End Select

Cells(Row, 4) = cbar.BuiltIn Row = Row + 1 Next cbar End Sub

When you work with toolbars, you can turn on the macro recorder to see what's happening in terms of VBA code. Most (but not all) of the steps that you take while customizing toolbars generate VBA code. By examining this code, you can discover how the object model for toolbars is put together. The object model actually is fairly simple and straightforward (for a change).

0 0

Post a comment