Excel 2000 drop-down fails with 2002

[Brian Kenny]

A long used form is failing with users that have upgraded to 2002.

Problem:
Excel 97/2000 users can choose items from in-cell drop-downs. Excel XP
users get "The cell...is protected...read-only." error message.

Scenario:
- Form is protected at both worksheet / workbook level.
- Drop-downs are of the Data, Data Validation, List variety.
- Source is a named range on a separate hidden, protected worksheet.
- Cells are locked to prevent values other than those in source lists.
- Form has been developed / maintained in a pure 2002 environment.

What's the story? I know that Excel 2002 offers new protection options
that aren't backwards compatible, but we're headed forward here.
Surely they didn't break this widely used feature?!

Is there an options change the 2002 users can make to enable
compatibility?

Can something be done from within 2000 that will get the drop-downs to
perform for XP users without affecting the 97/2000 community?

No, using drop-downs from the control toolbox is not an option.

Thanks in advance for your help - others have described similar
problems but I've seen no solutions posted.

cheers,
Brian

 

[Robert Rosenberg]

<<- Cells are locked to prevent values other than those in source lists>>

This is what's causing your problem. Excel 2002 and beyond changed/corrected
the protection behavior to one that makes more sense. You shouldn't be able
to enter data in a locked cell when protection is on regardless of the entry
method (Data-->Validation in your case).

You need to unlock those cells and let Data-->Validation take care of
preventing invalid entries (using the Error Alert tab).
_______________________
Robert Rosenberg
R-COR Consulting Services
Microsoft MVP - Excel
http://www.r-cor.com

 

[Harlan Grove]

<<- Cells are locked to prevent values other than those in source lists>>

This is what's causing your problem. Excel 2002 and beyond changed/corrected
the protection behavior to one that makes more sense. You shouldn't be able
to enter data in a locked cell when protection is on regardless of the entry
method (Data-->Validation in your case).

You need to unlock those cells and let Data-->Validation take care of
preventing invalid entries (using the Error Alert tab).

....

Philosophical digression: I don't agree that this makes more sense. Data
validation drop down lists in *LOCKED* cells in protected worksheets assure
that entries may *ONLY* be made from the validation list. If the cell were
unlocked (or the worksheet unprotected), *ANY* value could be *PASTED* into
a cell with any sort of validation. The Excel 97/2K functionality may have
seemed inconsistent, but it makes more sense in terms of securing entry into
cells with data validation list drop-downs. Pity this functionality has been
'corrected' in later versions.

 

[Brian Kenny]

nice one Robert - unlocking the cells was the trick - side benefit is
that 97/2000 users can now type in the drop-down cells if they know
the exact value they need and get an error alert nag if they get it
wrong

you saved me a lot of hassle as I haven't had time to set up a 2002
box and so was working in the dark - many thanks!

cheers,
Brian