Microsoft Office Tutorials and References
In Depth Information
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 , Ty pe , and
Name properties are copied to the worksheet beginning in row 4.
Dim c As CommandBar
For Each c In Application.CommandBars
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
Figure 17-2. The Index , Enabled , Visible , Type , and Name properties for each CommandBar
object are copied to an Excel worksheet.