How to skip certain command bar objects within a loop through the command bar collection

[elgatnegre]

Hi there!

I'm working in a macro that has a loop throught the command bar collection in order to get the name of each available command bar I have in excel and later do other stuff with it.

I need to skip certain command bar objects while doing the loop (few of them, e.g. I want to skip is the build-in command bar named "Formatting", and I've got their names)

How can I do that?

I've tried something like "If cbar.Name <> "Formatting" Then…" but I'm pretty new with vba and didn't know how to make it work.


This is my code:

Sub ShowHideCommandBarsGETVBA()


    Dim cbar As CommandBar
    Dim cbarname As Variant
    Dim Row As Long
    Dim textcode1 As String
    Dim textcode2 As String
    Dim textcode3 As String
    Dim textcode4 As String
    Dim textcode5 As String
    Dim textcode6 As String
    Dim textcode7 As String

    Row = 1
    
    For Each cbar In Application.CommandBars

        cbarname = Replace(cbar.Name, " ", "")
        
        textcode1 = "Sub ShowHide" & cbarname & " ()"
        textcode2 = "If Application.CommandBars(" & cbar.Name & ").Visible = True Then"
        textcode3 = "Application.CommandBars(" & cbar.Name & ").Visible = False"
        textcode4 = "Else:"
        textcode5 = "Application.CommandBars(" & cbar.Name & ").Visible = True"
        textcode6 = "End If"
        textcode7 = "End Sub"
        
        Cells(Row, 1) = textcode1
        Cells(Row + 1, 1) = Replace(Replace(textcode2, "(", "("""), ")", """)")
        Cells(Row + 2, 1) = Replace(Replace(textcode3, "(", "("""), ")", """)")
        Cells(Row + 3, 1) = Replace(Replace(textcode4, "(", "("""), ")", """)")
        Cells(Row + 4, 1) = Replace(Replace(textcode5, "(", "("""), ")", """)")
        Cells(Row + 5, 1) = Replace(Replace(textcode6, "(", "("""), ")", """)")
        Cells(Row + 6, 1) = Replace(Replace(textcode7, "(", "("""), ")", """)")
        
    Row = Row + 7


     Next cbar


End If
    
End Sub

 

[elgatnegre]

Sorry, in the code I attached to the post the last "End If" must be removed.