Validation list functionality disappered
 

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

Validation list functionality disappered
 

On Jan 30, 2:46*pm, Dr. Schwartz
wrote:
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

Validation list functionality disappered
 

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

Anyone else?

" wrote:

On Jan 30, 2:46 pm, Dr. Schwartz
wrote:
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

Validation list functionality disappered
 

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

Ron de Bruin has lots of notes he
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 DataValidation(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 wrote:

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

--

Dave Peterson

Validation list functionality disappered
 

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

The Doctor

"Dave Peterson" wrote:

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

Ron de Bruin has lots of notes he
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 DataValidation(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 wrote:

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

--

Dave Peterson