Validation list functionality disappered

[Dr. Schwartz]

In Excel 2003 I have a specific sheet where the validation list does not
appear (arrow button gone) when set. I can set it up (Data/Validation menu)
as usual with out problems. The other sheets in the workbook do not have this
problem.

I don't know if this has anything to do with the phenomenon, but I have
recently run this macro in the sheet:

Dim shp As Shape
For Each shp In ActiveSheet.Shapes
shp.Delete
Next

Can anyone help me get my validation lists back?

Thanks
The Doctor

 

[paul.robinson]

In Excel 2003 I have a specific sheet where the validation list does not
appear (arrow button gone) when set. I can set it up (Data/Validation menu)
as usual with out problems. The other sheets in the workbook do not have this
problem.

I don't know if this has anything to do with the phenomenon, but I have
recently run this macro in the sheet:

    Dim shp As Shape
    For Each shp In ActiveSheet.Shapes
        shp.Delete
    Next

Can anyone help me get my validation lists back?

Thanks
The Doctor

Hi
May be a mile off, but is your validation above a Freeze Panes line?
Seem to remember validation not working above a Freeze Pane, at least
in earlier Excel versions..
Paul

 

[Dr. Schwartz]

Thanks Paul, but I'm not using Freeze panes at all.

Anyone else?

 

[Dave Peterson]

The dropdown arrow in the data|validation cell is a shape.

Ron de Bruin has lots of notes here:
http://www.rondebruin.nl/controlsobjectsworksheet.htm

Including this warning:

<do> Not use code like below because it is possible that

It will delete the AutoFilter dropdowns
It will delete the Data>Validation(List option) dropdowns
Excel crash if there are comments on the sheet

Note: Not every Excel versions have all problems.
Sub NotUseThisMacro()
'Loop through the Shapes collection
Dim myshape As Shape
For Each myshape In ActiveSheet.Shapes
myshape.Delete
Next myshape
End Sub

========
I'm not sure if you can get that data|validation arrow back.

Maybe you could delete the row and insert a new row and put it back.

(or copy the data to a new sheet and rebuild the data|validation.)

====
I tried searching google for solutions (other people have damaged the
data|validation arrows, too!). But I couldn't find enough to remind me of the
solution that worked--sorry.

 

[Dr. Schwartz]

Once again you saved my day Dave - thank you for solving this mystery. I
ended up moving to a new sheet.

The Doctor