Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protecting sheet but keeping list functionality in 2003 | Excel Worksheet Functions | |||
Using list functionality | Excel Worksheet Functions | |||
YIKES! All Validation functionality DISAPPEARED! | Excel Worksheet Functions | |||
List box setup, placement, and functionality | New Users to Excel | |||
Autofill functionality with Validation | Excel Programming |