List Excel command bars and controls

Recently I had to update some old Visual Basic code to talk to a new third-party Excel toolbar (Visual Basic refers to toolbars as CommandBars internally). To do this I needed to know the names of the toolbars and toolbar buttons. I found a couple of pieces of code online that came close but didn’t quite get me what I needed. So I threw this together.

Sub ListCommandBarsAndControls()

    Set ws = ThisWorkbook.Sheets("Sheet1")
    
    ' Create a header on the first row of the spreadsheet
    ws.Rows(1).Cells(1) = "BAR NAME"
    ws.Rows(1).Cells(2) = "BAR VISIBLE"
    ws.Rows(1).Cells(3) = "BAR BUILTIN"
    ws.Rows(1).Cells(4) = "CONTROL ID"
    ws.Rows(1).Cells(5) = "CONTROL CAPTION"
    ws.Rows(1).Cells(6) = "CONTROL ENABLED"
    
    ' Set a variable so the following starts writing on the second row
    targetRow = 2
    
    ' Iterate through all command bars
    For i = 1 To Application.CommandBars.Count

        Set sCmdBar = Application.CommandBars(i)
        ws.Rows(targetRow).Cells(1) = sCmdBar.Name
        ws.Rows(targetRow).Cells(2) = sCmdBar.Visible
        ws.Rows(targetRow).Cells(3) = sCmdBar.BuiltIn
        
        ' And for each command bar, iterate through all the available controls
        For j = 1 To Application.CommandBars(i).Controls.Count
            Set sControl = Application.CommandBars(i).Controls(j)
            ws.Rows(targetRow).Cells(1) = sCmdBar.Name
            ws.Rows(targetRow).Cells(2) = sCmdBar.Visible
            ws.Rows(targetRow).Cells(3) = sCmdBar.BuiltIn
            ws.Rows(targetRow).Cells(4) = sControl.ID
            ws.Rows(targetRow).Cells(5) = sControl.Caption
            ws.Rows(targetRow).Cells(6) = sControl.Enabled
            targetRow = targetRow + 1
        Next j

    Next i

    ' Some times it takes a while to complete so pop up 
    ' a message box to make it clear when it's finished
    MsgBox "Complete!"

End Sub

If you create a new macro, paste this code in and run it you should get output that looks something like the following screenshot. Note that I filtered the output here after running the script to only show me information about toolbars that aren’t built into Excel, i.e. third-party toolbars that are generated by Excel plugins.