Gary taught me Excel and much else.
--
Gary''s Student - gsnu200840


"Brettjg" wrote:

Hi GS, well I'll be damned! Peter T has a nice little line that should solve
the problem, but to be frank, it's a ridiculous little bug in Excel. Who
would ever want to delete the dropdowns and retain the validation?

Anyway, on another matter, who the hell is Gary? Regards, Brett


"Gary''s Student" wrote:

Sadly, the Data Validation drop-down is consider to be a Shape. If you
insert Data Validation on two cells, one with only a message-on-click and the
other with a drop-down and then put some other Shape (say something from the
Drawing Toolbar) on the sheet as well and run:

Sub dural()
Dim s As Shape
For Each s In ActiveSheet.Shapes
MsgBox (s.Name)
s.Delete
Next
End Sub

You will see that both the "real" shape and the drop down are called out and
deleted. The Validated cell that only displays a message when clicked will
not be disturbed.
--
Gary''s Student - gsnu200840


"Brettjg" wrote:

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