Validation list functionality disappered

D

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
 
P

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
 
D

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.
 
D

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top