Page 395 - Microsoft Office Excel 2003 Programming Inside Out

Part 5: Manipulating Excel Objects
Command Bars

Listing CommandBar Objects
Excel maintains a large collection of built-in command bars, which you can list using the
following routine. (See Figure 17-2.) This routine begins by making the ListCommandBars
sheet active and then it uses a For Each loop to scan through the CommandBars collection.
(You can change this to “Sheet3” or any other existing sheet name you specify.) Using the
CommandBar object from the For Each statement, the Index, Enabled, Visible, Type, and
Name properties are copied to the worksheet beginning in row 4.

Sub ListCommandBars()
Dim c As CommandBar
DimiAsLong

Sheets.Item("ListCommandBars").Activate
i=3
For Each c In Application.CommandBars
i=i+1
ActiveSheet.Cells(i, 1) = c.Index
ActiveSheet.Cells(i, 2) = c.Enabled
ActiveSheet.Cells(i, 3) = c.Visible
ActiveSheet.Cells(i, 4) = c.Type
ActiveSheet.Cells(i, 5) = c.Name
Next c
End Sub

Chapter 17

Figure 17-2. The Index, Enabled, Visible, Type, and Name properties for each CommandBar
object are copied to an Excel worksheet.

369