My Validation Lists Won't Work, Help!

C

Centurius

I have a quite complex workbook that i created (with the help of the
experts on these groups. thanks again.) last year which, by using
validation lists and very basic VBA lets me enter from drop-downs, all
the information about an upcoming job, which we get the client to sign,
and we're right to start manufacturing. Now i've just gone to open it
and create a new job details sheet, and found that none of the
drop-downs in the sheet work, i've checked the validation and all the
named ranges are still in there, and still pointing to the right lists.

i've tried creating new simple validation lists, with just a custom
list on the same worksheet but these dont work either. the arrow does
not appear beside the cell with the validation.

i've checked that the "in cell dropdown" option is ticked,
i've checked the workbook automatically re-calculates,
i've checked that the workbook isnt shared,
i've checked the ranges are all still valid references..

i'm stumped!

please help

thanks in advance

Dan
centurius(at)hotmail.com
 
C

Centurius

also forgot to add that validation still works in other workbooks, and
when i create new (blank) workbooks

thanks again

Dan
 
C

Centurius

also forgot to add that validation still works in other workbooks, and
when i create new (blank) workbooks

thanks again

Dan
 
C

Centurius

also forgot to add that validation still works in other workbooks, and
when i create new (blank) workbooks

thanks again

Dan
 
R

Roger Govier

Hi Dan

I have been wrestling with a similar problem most of this week. I have
searched Google high and low, and eventually found (just an hour or so
ago) what appears to be the answer to my problem.
I my case, I get no problem at all with XL2003, but when I install the
workbook on a client's network with XL2000, drop downs appear for a
while, then suddenly disappear. If you close the workbook, then open
again, then they appear for a while then disappear again.

The cause appears to be copying a sheet from the workbook out to another
file.
Rather than having a separate template for the Purchase order form, the
2 different Order form layouts are sheets within the main workbook.
To create a new orderform, I use
Sheets("SD Order").Copy or Sheets("KC Order").Copy
which creates a new Workbook with a copy of the sheet. The user fills in
details on here from dropdown's, then it prints, writes details back to
a ledger within the source workbook and closes the new workbook without
saving it.

After 3 or 4 new PO's have been raised, the dropdowns on the new PO fail
to appear, as do dropdowns on Autofilter on other sheets in the main
workbook, even though Autofilter is still applied. It appears that
XL2000 runs out of resources after this operation has been continued a
few times. Closing the file and starting again, seems to refresh it.

The other poster had exactly this problem with XL2000, but not with XL97
or XL2002/2003.
I did not have the problem when using separate templates, so I am about
to take the sheets back out to separate .xlt files, and open each when I
need to create a new order. When I did this originally, I had no
problems (it's a long story as to why they were moved into the main
book!!!), but I now need to carry out some exhaustive testing to satisfy
myself that the problem is truly solved by this change back.

I do hope that you are using XL2000 and that your problem is similar. I
also hope that my post can save you the hours of agony and frustration I
have undergone during the past few days.
 
C

Centurius

i'm using XL 2003 (XP) :(

and i've opened and closed this workbook a dozen times or so and they
don't work from opening, so doesnt sound like quite the same problem...

looks like its hours of agony and frustration for me for the next few
days
but thanks for the thought, i'll look into it anyway
 
C

Centurius

also forgot to add that validation still works in other workbooks, and
when i create new (blank) workbooks

thanks again

Dan
 
C

Centurius

after fiddling some more with this, i've found that the validation is
actually working, its just the in-cell drop downs that aren't
appearing.

it allows me to enter the correct data but if i enter somthing not in
the validation list then it returns my error message.

this is getting frustrating as the validation lists are too long to
remember each and every entry, i need those drop down lists back!
 
D

Dave Peterson

Do you use xl97?

Is the cell with data|validation above the window|split line?

If yes to both, then this is a bug that was fixed in xl2k. You could remove the
window|split and get the dropdown arrow.
 
D

Debra Dalgleish

Another thing to check is Tools>Options, View tab
Objects should be set to Show All or Show Placeholders
 
C

Centurius

i'm using Excel XP, and i have checked the Tools>Options, View tab
Objects and it is set to "Show All"

i'm still stumped on this one if anyone else has ideas.. i can send a
copy to anyone if you think that would help?
let me know at dan (at) coastcars.net
 
R

Roger Govier

Hi Dan

Not sure if I can help, but I'd be happy to take a look at the file if
you want to mail it direct to me.
After all me trials and tribulations with a similar problem, there might
be something I can spot.
Remove NOSPAM from my address.
 
C

Centurius

turns out there was some form of corruption with one of my sheets, just
copied the contents of that sheet, and the code relating to it, to a
blank one, and updated all my links and hey presto, seems to be running
fine.

thanks roger, ten thumbs up!

cheers
dan
 
R

Roger Govier

Hi Dan

You're very welcome. It was a very frustrating error, but once I'd found
the problem sheet all did appear to go well.
Glad you're up and running again.
 
C

Centurius

i probably should have provided a few tips as to how to navigate the
workbook, its a bit tricky with all the buttons and forms and stuff
adding bits everywhere
 
Top