Data Validation drop-down list does not appear

  • Thread starter Thread starter Barbara
  • Start date Start date
B

Barbara

I have an Excel workbook that has data validation lists.
The sources for the lists are ranges. On one worksheet,
the drop-down list does not appear on any cell with data
validation. On all other sheets, data validation works
fine.
 
In Excel 97, if the cell with data validation is in a frozen pane of the
window, the dropdown won't work. If that's the cause, perhaps you could
split the window, instead of freezing the panes.
 
Thanks for the responses.

I do have all of the settings correct - as a matter of
fact, the drop downs did appear at one point and now they
are no longer there, though no changes were made to the
data validation settingse. I am currently working in
Excel 2002.
 
Did you move the list? If you want, send the file and I will try to help.
Very often these things have a simple, easily-overlooked solution. Of
course, you'll have to remove the extraneous garbage from my address.
 
Are objects visible?
Choose Tools>Options, View tab
Under Objects, select Show All or Show Placeholders.
 
Show All was checked. It's really the weirdest thing.
The drop-down lists were all visible at one point and now
they are not. Nothing was changed on the sheet to prompt
this. The validation setup is correct. I think somehow
the sheet got corrupted. Even if I attempt to make a
simple data validation with list values submitted directly
in the dialogue box (not using a range), the drop-down
list does not appear. I do the same on any other sheet in
the workbook and it works fine. I copy the sheet to a new
book, and the same problem occurs on the copied sheet.
 
It sounds as though it could be corrupted, in which case a rebuild would
be a good idea.
 
I had the exact same problem, and found that a macro i run corrupts the
sheet:

Sub CorruptSheet()
Dim Thing As Shape
' Delete all the shapes on the worksheet
For Each Thing In ActiveSheet.Shapes
MsgBox "Delete " & Thing.Name
Thing.Delete
Next Thing
End Sub

Copying the worksheet, or re-applying the cell validation was not
successful, as above. To fix the problem i copied all the cells to new
sheet.


- Kevin
 
Back
Top