How do I delete all buttons on a worksheet?

Question

I've got a bit of code that creates a Save button on a worksheet (held in the wsReport variable), but it doesn't remove previous buttons. Over time, they tend to build up. Is there any way to do something like this?

wsReport.Buttons.All.Delete

(Not right, obviously, but gives an idea of what I'm looking for.)

Possible duplicate of [Deleting a collections of VBA buttons](https://stackoverflow.com/questions/8898976/deleting-a-collections-of-vba-buttons) — Liam, Jul 27 '18 at 14:16

score 17 · Accepted Answer · edited May 23 '17 at 11:46

See code below :)

Sub RemoveButtons()
Dim i As Integer
    If ActiveSheet.ProtectContents = True Then
        MsgBox "The Current Workbook or the Worksheets which it contains are protected." & vbLf & "                          Please resolve these issues and try again."
    End If

    On Error Resume Next
        ActiveSheet.Buttons.Delete
End Sub

Source: http://www.mrexcel.com/forum/excel-questions/609668-delete-all-buttons-sheet-visual-basic-applications.html

or could you use code below: (Buttons in VBA are in the Shapes collection).

Sub DelButtons()
Dim btn As Shape

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

End Sub

source: Deleting a collections of VBA buttons

`Activesheet.Buttons.Delete` worked very nicely all on its own - many thanks! — Andrew Perry, Feb 15 '16 at 14:31

score 3 · Answer 2 · edited Aug 28 '19 at 13:30

3

See the code below:

Sub DeleteAllShapes() 

    ActiveSheet.Shapes.SelectAll

    Selection.Delete

End Sub

edited Aug 28 '19 at 13:30

Shayki Abramczyk

36,824
16
89
114

answered Aug 28 '19 at 13:08

Info City

31
1

score 1 · Answer 3 · answered Jun 26 '20 at 09:12

1

If somebody found this question, but needs to delete only one button, this helped me:

ActiveSheet.Shapes("my_button_name").Delete

answered Jun 26 '20 at 09:12

elano7

1,584
1
18
18

score 0 · Answer 4 · answered Oct 16 '17 at 19:01

0

There's an easier method that doesn't use VB:

Click the Developer tab
Click Design Mode
Click on any button in the worksheet
Press Ctrl + A to select all buttons in the worksheet
Press Delete

answered Oct 16 '17 at 19:01

thatguy

47

You need to activate Developer Tab first: [Display the Developer tab in the toolbar](https://www.techonthenet.com/excel/questions/developer_tab2013.php) – Ali Şentürk May 23 '20 at 00:29

How do I delete all buttons on a worksheet?

4 Answers4