List all Command Bar Properties in Excel

February 1, 2010 by JP ·

If you work with Command Bars in Excel, or write add-ins for Excel 2003 that add custom menu options, you'll need to know various properties such as their names. A basic subroutine would look like this:

Sub ListCommandBars()
  Dim cBar As Office.CommandBar

  For Each cBar In CommandBars
    Debug.Print "Name: " & cBar.Name
    Debug.Print "Local name: " & cBar.NameLocal
    Debug.Print "Is Visible: " & cBar.Visible
    Debug.Print "Index: " & cBar.Index
    Debug.Print "Is Built-In: " & cBar.BuiltIn
    Debug.Print "# Of Controls: "; cBar.Controls.Count
    Debug.Print "Enabled: " & cBar.Enabled
    Debug.Print "Type: " & cBar.Type
    Debug.Print "---"
  Next
End Sub

This is a simple sub that loops through each CommandBar Object and prints most of the important properties to the Debug Window. The result looks something like this:

Name: Worksheet Menu Bar
Local name: Worksheet Menu Bar
Is Visible: True
Index: 1
Is Built-In: True
# Of Controls: 10
Enabled: True
Type: 1
—
Name: Chart Menu Bar
Local name: Chart Menu Bar
Is Visible: False
Index: 2
Is Built-In: True
# Of Controls: 10
Enabled: True
Type: 1
—
Name: Standard
Local name: Standard
Is Visible: True
Index: 3
Is Built-In: True
# Of Controls: 24
Enabled: True
Type: 0
—
Name: Formatting
Local name: Formatting
Is Visible: True
Index: 4
Is Built-In: True
# Of Controls: 19
Enabled: True
Type: 0
—