Programming Excel with VBA and .NET

Name

commandbar.Controls

Synopsis

Returns the CommandBarControls collection used to add controls to the command bar and to get controls from the command bar. For example, the following code displays worksheet menu bar controls three levels deep:

' List Worksheet menus three levels deep.
Sub ListWorksheetMenus( )
    Dim menu As CommandBarControl, item As CommandBarControl, _
      subitem As CommandBarControl
    Debug.Print "Worksheet Menu Bar"
    For Each menu In Application.CommandBars("Worksheet Menu Bar").Controls
        Debug.Print , menu.Caption
        For Each item In menu.Controls
                Debug.Print , , item.Caption, item.id, item.Tag
                If item.Type = msoControlPopup Then
                    For Each subitem In item.Controls
                            Debug.Print , , , subitem.Caption, _
                              subitem.id, subitem.Tag
                    Next
                End If
        Next
    Next
End Sub