Delete buttons on sheet

[ErikJan]

I use this (and I believe it worked fine before):

Code: Select all

        Dim btn As Shape
         For Each btn In ActiveSheet.Shapes
            If btn.Type = 8 Then btn.Delete
        Next

All looks fine, in the loop I get hits for buttons when btn.type=8 and even btn.name is correct, still I get an error on the btn.delete...

2013-10-22 21_42_42-Microsoft Visual Basic.png

Anyone who has an idea how to avoid this...?

[HansV]

When deleting items from a collection, it's best to loop backwards:

Code: Select all

    Dim i As Long
    For i = ActiveSheet.Shapes.Count To 1 Step -1
        If ActiveSheet.Shapes(i).Type = 8 Then
            ActiveSheet.Shapes(i).Delete
        End If
    Next i

[ErikJan]

I just found and tried exactly that... same problem...

[ErikJan]

Update: it already works and I now understand why it worked before... In this code update my worksheet was protected... Once I removed that, all was fine again. I apologize...

[rory]

If you just want to delete Forms buttons:

Code: Select all

Activesheet.Buttons.Delete

should be enough.