Worksheet Menu Bar Buttons

[Jaye7]

Can anyone provide a script that lists the caption names of all work sheet commandbar buttons, one script to list them on a worksheet and another to list them in a listbox.

Thanks

 

[pbornemeier]

This code does not do anything iwth the Ribbon controls

Sub ListAllButtonsPopulateListBox()
 
    'Code requires a userForm1 with ListBox1
 
    Dim lX As Long
    Dim lY As Long
    Dim sPrintLine As String
    Dim lNextWriteRow As Long
    Dim aryButtonNames() As Variant
 
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("Document.CmdBarAndBtns").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(before:=Sheets(1)).Name = "Document.CmdBarAndBtns"
 
    lNextWriteRow = 1
 
    For lX = 1 To CommandBars.Count
        For lY = 1 To CommandBars(lX).Controls.Count
            Cells(lNextWriteRow, 1) = CommandBars(lX).Controls(lY).Caption
            'if you want to populate a list box then something line this:
            ReDim Preserve aryButtonNames(1 To lNextWriteRow)
            aryButtonNames(lNextWriteRow) = CommandBars(lX).Controls(lY).Caption
            lNextWriteRow = lNextWriteRow + 1
        Next
    Next
 
    'Next line will remove the & from each of the captions (used to mark shortcut keys)
    Cells.Replace What:="&", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
    UserForm1.ListBox1.List = aryButtonNames
 
    UserForm1.Show
 
End Sub

 

[Jaye7]

Phil, that's exactly what I wanted.

Now that the button names are listed in the listbox can we search the listbox for the name "Access Scripts" and if found then it will delete that commandbar button.

Sometimes the button Access Scripts is not present therefore a normal delete script bugs out even if I use an on error script, that is why I wanted to list the buttons first and then delete it from there.

If I have to create a new thread for this that is fine.

 

[pbornemeier]

You could also have the code do the search and delete the command bars if they are found. lX counts backwards in case more then one command bar has the caption you are searching for. Replace the For-Next loops in my previous post with this:

    bFound = False
    For lX = CommandBars.Count To 1 Step -1
        For lY = 1 To CommandBars(lX).Controls.Count
            If CommandBars(lX).Controls(lY).Caption = "Access Scripts" Then
                bFound = True
                Exit For
            End If
        Next
        If bFound Then
            CommandBars(lX).Delete
            bFound = False
        End If
    Next

 

[Jaye7]

Phil,

I modified your code as you advised, however it fails to delete the command bar button, the error says.

method delete of object commanbar failed
run-time error -2147467259

Fails in Excel 2000 and 2010 on line

CommandBars(lX).Delete

Sub newTEST()
 
    'Code requires a userForm1 with ListBox1
 
    Dim lX As Long
    Dim lY As Long
    Dim sPrintLine As String
    Dim lNextWriteRow As Long
    Dim aryButtonNames() As Variant
 
    Application.DisplayAlerts = False
    On Error Resume Next
    Worksheets("Document.CmdBarAndBtns").Delete
    On Error GoTo 0
    Application.DisplayAlerts = True
    Worksheets.Add(before:=Sheets(1)).Name = "Document.CmdBarAndBtns"
 
    lNextWriteRow = 1
 
   bFound = False
    For lX = CommandBars.Count To 1 Step -1
        For lY = 1 To CommandBars(lX).Controls.Count
            If CommandBars(lX).Controls(lY).Caption = "Access Scripts" Then
                bFound = True
                Exit For
            End If
        Next
        If bFound Then
           [COLOR=black]CommandBars(lX).Delete  ' FAILS HERE[/COLOR]
            bFound = False
        End If
    Next
 
    'Next line will remove the & from each of the captions (used to mark shortcut keys)
    Cells.Replace What:="&", Replacement:="", LookAt:=xlPart, SearchOrder:= _
        xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False
 
    UserForm1.ListBox1.List = aryButtonNames
 
    UserForm1.Show
 
End Sub

 

[Jaye7]

Solved it, thanks very much Phil.

CommandBars(lX).Controls(lY).Delete