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

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" <(E-Mail Removed)> wrote in message
news:6AB38A84-D2EF-4951-831A-(E-Mail Removed)...
> 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

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

"Tim Williams" wrote:

> 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" <(E-Mail Removed)> wrote in message
> news:6AB38A84-D2EF-4951-831A-(E-Mail Removed)...
> > 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
>
>
>

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

"Shawn" wrote:

> 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