Drop down list missing.

  • Thread starter Thread starter Dennis Saunders
  • Start date Start date
D

Dennis Saunders

Has anyone solved the mystery of Data Validation drop downs going AWOL?
I ran a macro to clear Web stuff (shapes, hyperlinks etc.) and its
impossible to get DV back on this sheet. I had to copy and paste to another
sheet and re-enter the DV.
 
Deleting all shapes can delete the arrows for the autofilter.

This is not always good:
Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next shp
 
And it can delete the data validation dropdowns too. <g>

AFAIK, there's no way to get them back on that sheet, but you may be
able to copy everything, and paste onto a new sheet.
 
I tried this using xl2003 (not an exhaustive test, though).

I could turn of data|filter|autofilter and then turn it back on and the arrows
for the autofilter came back.

I don't recall them coming back in earlier versions.

The arrows for the data|validation cells didn't come back, though.

(minor testing at best.)
 
You can always test for it

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
If shp.Type = msoFormControl Then
If shp.FormControlType <> xlDropDown Then
shp.Delete
End If
Else
shp.Delete
End If
Next shp

The only problem I can see is the forms combobox which is Type
msoFormControl, and FormControlType xlDropDown, so it won't get deleted.

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
I replied with a response for the arrows in data|filter|autofilter. But Debra
tried to correct me. (I missed the subject in almost all my responses!)

Sorry.
 
Maybe you can test the .topleftcell address. Those data|validation dropdowns
don't seem to have one.

Option Explicit
Sub testme()

Dim shp As Shape
Dim testStr As String
Dim OkToDelete As Boolean

For Each shp In ActiveSheet.Shapes
OkToDelete = True

testStr = ""
On Error Resume Next
testStr = shp.TopLeftCell.Address
On Error GoTo 0

If shp.Type = msoFormControl Then
If shp.FormControlType = xlDropDown Then
If testStr = "" Then
'keep it
OkToDelete = False
End If
End If
End If

If OkToDelete Then
shp.Delete
End If

Next shp

End Sub

And this saved the autofilter dropdown arrows, too. (Still beating that
horse!).

I like this one, Bob.
 
Dave Peterson said:
Maybe you can test the .topleftcell address. Those data|validation dropdowns
don't seem to have one.

snip

And this saved the autofilter dropdown arrows, too. (Still beating that
horse!).

I like this one, Bob.

So do I now :-)
 
Just one tiny lateral Q, guys, since Dave P has got me a little worked up
<bg>

How does one prevent the risk of DV drop down sudden disappearance on a
particular sheet (where there's absolutely no "incriminating" macros in the
picture) ?
 
I don't think you can prevent it. That macro could be in any workbook that's
open.

And you could kill all of data|validation just by a simple copy|paste.

(Although, I've never seen it disappear without a good reason--usually my
mistake!)
 
... it (DVs) disappear without a good reason
... and unfortunately, it seems, this event is precisely what happened
 
Back
Top