Deleting all buttons in a collection

[Ken Loomis]

Not sure what the correct reminology is, but I need to delete all the
buttons on a worksheet.

Currently I use this code:

Sub DeleteButtons()
'
' DeleteButtons Macro
' Macro recorded 3/24/05 by Ken
'

'
ActiveSheet.Shapes("Rectangle 4").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 5").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 8").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 7").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 6").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 3").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 2").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 1").Select
Selection.Cut
ActiveSheet.Shapes("Rectangle 9").Select
Selection.Cut
End Sub

But now I realize that if I add a button, I also have to add this:

ActiveSheet.Shapes("Rectangle 9").Select
Selection.Cut

for that new button. Also, it occurs to me that I do not need to select the
button first to delete it.

In pseudo code I think it would be something like this:

For each button in the collection of buttons on this sheet
Delete this button
Next Button

Can someone please suggest the VBA code that will do this? It woul dbe
helpful if you could suggest the syntax to specifiy a specific worksheet in
the active workbook, since the buttons are only on the "Start Here"
worksheet

Thanks,
Ken

 

[Norman Jones]

Hi Ken,

Would this work for you:

Sheets("Start Here").DrawingObjects.Delete

 

[Ken Loomis]

Thanks, Norman. That works great.

Ken

Hi Ken,

Would this work for you:

Sheets("Start Here").DrawingObjects.Delete

 

[Tim Williams]

Ken,

Norman already posted the answer, but do you have any items on the
sheet you *don't* want to delete ?

Tim.

 

[Ken Loomis]

Thanks for sking, Tim, but on this particular worksheet, I do want to delete
all the drawing objects.

I assume you were going to suggest an alternative that would let me delete
just some of the drawing objects. If so, I'd like to see your idea to learn
more about how I can access & manipulate the collection of drawing obkects
on a sheet.

TRhanks,
Ken

 

[Norman Jones]

Hi Ken,

Here is one way to selectively delete:

Sub Tester()
Dim shp As Shape
Dim arr As Variant
Dim ws As Worksheet

Set ws = ActiveSheet
arr = Array("Rectangle 2", "Rectangle 4", "Oval 7") '<<==KEEP!

For Each shp In ws.Shapes
If IsError(Application.Match(shp.Name, arr, 0)) Then
shp.Delete

End If
Next
End Sub