Data Validation Lost After Clearing All Shapes

[Shawn]

I'm trying to delete all shapes on a sheet which works using the below code,
but a problem I'm having is that any validation list on the same sheet is
also deleted, and once delete validation lists will NOT appear on that same
sheet again even after restarting excel.

Does anyone know how to either clear all shapes but NOT validation lists, or
have any knowledge about why this occurs and why you then can't add a new
validation list (If I goto data > validation the list settings are correct
and reference the list named range - doing the same on another tab works
correctly).

For Each oShape In Sheets("Sheet1").Shapes
oShape.Delete
Next oShape

 

[Tim Williams]

You could try using the drawingobjects collection instead.
It doesn't *seem* to include the validation drop-downs.

For Each drw In Sheets("Sheet1").DrawingObjects
....
Next drw


Tim

 

[Shawn]

Good suggestion, but it ended up giving me an error (Delete method of picture
class failed - possible it's not .Delete). I figured out that the following
works. Strange part is that I still can't enter validation lists on the sheet
I used the other code on unless I create a new sheet or workbook.

For Each oShape In Sheets("Sheet1").Shapes
If oShape.Type = msoPicture Then oShape.Delete
Next oShape

 

[Eric G]

How about:

Sub test()
Dim oshape As Shape
Dim testIt As Boolean
'
For Each oshape In Sheets("Sheet1").Shapes
On Error Resume Next ' Non drop-downs cause an error two lines down!
testIt = False
testIt = oshape.FormControlType = xlDropDown
If (Not testIt) Then oshape.Delete
Next oshape
End Sub

HTH,

Eric