Deleting a collections of VBA buttons

Question

I'm using the following script to produce buttons in excel, the range is just where I'd like it to be placed.

Sub CreateAddButton(rng As Range)
    Dim btn As Button
    With Worksheets("User")
        Set btn = .Buttons.Add(rng.Left, rng.Top, rng.width, rng.Height)
        With btn
           .name = "Add"
           .Caption = "Add Column"
           .OnAction = "CreateVariable"
        End With
    End With
End Sub

Only problem is, I'd like a method which can delete all the buttons produced by this method? I want to steer away from global variables if possible. Any help would be gratefully received. James

You could tag all buttons by using a specific prefix or suffix in the name: then you could loop through all the buttons on the sheet and delete any where the name contained that prefix/suffix. — Tim Williams, Jan 17 '12 at 17:49
I'd have to you some sort of golbal variable to do that, and I'm trying to get rid of all global variables, as they lose their value if there's an error in excel. — James, Jan 18 '12 at 09:14
Why would you need a global variable to do that? You don't! (Unless you count your whole Excel instance as a global variable...) See @brettdj's implementation. — Jean-François Corbett, Jan 18 '12 at 11:36

score 4 · Accepted Answer · answered Jan 18 '12 at 11:17

I'd suggest Tim's method using a specific name - which does't need a global variable. For example you could add a "_||ForDeletion" suffix to each button name and then look for it on a delete routine

 .Name = "Add_||ForDeletion"

A Forms button does provide another alternative though (no pun intended), you can store a text sting in the AlternativeText under "Properties" then "Web" and use this as an identifier for a delete routine.

The delete routine at bottom works backwards to avoid errors when looping through a range

enter image description here

Sub TesMe()
    Call CreateAddButton([a2])
End Sub

Sub CreateAddButton(rng As Range)
    Dim btn As Button
    With Worksheets("User")
        Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
        With btn
            .Name = "Add"
            .Caption = "Add Column"
            .OnAction = "CreateVariable"
            .ShapeRange.AlternativeText = "MyCollection"
        End With
    End With
End Sub


Sub GetMyButtons()
    Dim btns As Object
    Dim lngRow As Long
    Set btns = Sheets("User").Buttons
    For lngRow = btns.Count To 1 Step -1
        If btns(lngRow).ShapeRange.AlternativeText = "MyCollection" Then
            MsgBox "Found one", vbCritical
            btns(lngRow).Delete
        End If
    Next
End Sub

score 0 · Answer 2 · answered Jan 17 '12 at 17:52

0

Buttons in VBA are in the Shapes collection. You can use:

Sub deleteButtons()
Dim btn As Shape

For Each btn In ActiveSheet.Shapes
    If btn.AutoShapeType = msoShapeStyleMixed Then btn.Delete
Next

End Sub

msoShapeStyleMixed seems to be the type for all Form and ActiveX controls.

answered Jan 17 '12 at 17:52

Jesse

1,937
3
19
28

That would be great, but there are some buttons I need to keep on the page. I think the only way I can identify the buttons I want to delete is by either the name or the caption properties. – James Jan 18 '12 at 09:15

Deleting a collections of VBA buttons

2 Answers2

Linked