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

Hi Ken,

Would this work for you:

Sheets("Start Here").DrawingObjects.Delete

---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Thanks, Norman. That works great.

Ken


"Norman Jones" wrote in message
...
Hi Ken,

Would this work for you:

Sheets("Start Here").DrawingObjects.Delete

---
Regards,
Norman



"Ken Loomis" wrote in message
...
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

Ken,

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

Tim.


"Ken Loomis" wrote in message
...
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

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


"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Ken,

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

Tim.


"Ken Loomis" wrote in message
...
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

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

---
Regards,
Norman



"Ken Loomis" wrote in message
...
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


"Tim Williams" <saxifrax@pacbell*dot*net wrote in message
...
Ken,

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

Tim.


"Ken Loomis" wrote in message
...
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