CODEWITHSUNDEEP
excelvba
Andrew Perry
Andrew Perry

Reputation: 765

How do I delete all buttons on a worksheet?

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.)

Upvotes: 11

Views: 60103

Answers (4)

elano7
elano7

Reputation: 2255

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

ActiveSheet.Shapes("my_button_name").Delete

Upvotes: 1

Info City
Info City

Reputation: 31

See the code below:

Sub DeleteAllShapes() 

    ActiveSheet.Shapes.SelectAll

    Selection.Delete

End Sub

Upvotes: 3

thatguy
thatguy

Reputation: 47

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

  1. Click the Developer tab
  2. Click Design Mode
  3. Click on any button in the worksheet
  4. Press Ctrl + A to select all buttons in the worksheet
  5. Press Delete

Upvotes: 0

XsiSecOfficial
XsiSecOfficial

Reputation: 964

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

Upvotes: 17

Related Questions