See my page
http://www.rondebruin.nl/controlsobjectsworksheet.htm

This will work for all

Sub Shapes1()
'Delete all Objects except Comments
On Error Resume Next
ActiveSheet.DrawingObjects.Visible = True
ActiveSheet.DrawingObjects.Delete
On Error GoTo 0
End Sub

Or

This example avoid the problem of losing AutoFilter and
Data Validation dropdowns on your worksheet when you use Type 8.

Sub Shapes4()
'Dave Peterson and Bob Phillips
'Example only for the Forms controls
Dim shp As Shape
Dim testStr As String

For Each shp In ActiveSheet.Shapes

If shp.Type = 8 Then
If shp.FormControlType = 2 Then
testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0
If testStr < "" Then shp.Delete
Else
shp.Delete
End If
End If

Next shp
End Sub



--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm




"Brettjg" wrote in message ...
Hi Peter, seems this topic has created some interest. Even Gord Dibben didn't
know of this, and that IS saying something. Regards, Brett

"Peter T" wrote:

If you want to delete shapes except DV / filter drop-downs simply use

ActiveSheet.DrawingObjects.Delete

Regards,
Peter T


"Brettjg" wrote in message
...
Hi Jon, yes I think it does. Regards, Brett

"Jon Peltier" wrote:

If you were wary, now, it wouldn't be a trap!

I never thought about this either. I wonder if it hoses the AutoFilter
dropdowns as well.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services, Inc.
http://PeltierTech.com/WordPress/
_______


"Brettjg" wrote in message
...
Thanks Peter, that'll do it. Talk about a trap for the unwary! Regards,
Brett

"Peter T" wrote:

You could include any of these checks before deleting the DV dropdown

If TypeName(sh.DrawingObject) < "DropDown" Then
If sh.Type < msoFormControl Then ' any type of Form control
If left$(sh.Name,9) < "Drop Down" Then

Regards,
Peter T



"Brettjg" wrote in message
...
In an extraordinarily weird week with VB, this is the weirdest of
all.
I
have :
Dim Shp As Shape

and futher on:

Range("title.1:clean.end.personal").ClearComments
'
'validation settings for cells are still there

On Error Resume Next
For Each Shp In ActiveSheet.Shapes
Shp.Delete
Next

''validation settings for cells are gone

I have isolated the code to the area between the two comments (which
say
what is happening). How can this possibly be? Earlier in the macro I
have
run
the identical code in a different sheet and validation settings are
unchanged. EH?
Regards, Brett










__________ Information from ESET Smart Security, version of virus signature database 3954 (20090323) __________

The message was checked by ESET Smart Security.

http://www.eset.com




__________ Information from ESET Smart Security, version of virus signature database 3954 (20090323) __________

The message was checked by ESET Smart Security.

http://www.eset.com