Excel 2000 drop-down fails with 2002

  • Thread starter Thread starter Brian Kenny
  • Start date Start date
B

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
 
<<- 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
 
Robert Rosenberg said:
<<- 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).
Click to expand...
....

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

I just found a similar problem (drop-downs do not appear). This occurred in a Workbook created in Excel 97 or 2000 and displayed in Excell 2002

The Data Validation was correctly set to have Data Validation of kind "List". The list concerned was a Defined Name in another worksheet in the open workbook.

The drop-down arrows worked correctly in the worksheet containing the Defined Name, but clicking in cells defined with the same validation in the other worksheet produced no action at all. The worksheet was not protected and there were no frozen panes

It appears to have been a corrupt worksheet: I have never heard of a corrupt worksheet in Excel before, I did not know they could be. I have fixed it by creating a new worksheet and pasting the old sheet into it

Was there a simpler solution

Cheer

John McGhi


----- Robert Rosenberg wrote: ----

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

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

You need to unlock those cells and let Data-->Validation take care o
preventing invalid entries (using the Error Alert tab)
______________________
Robert Rosenber
R-COR Consulting Service
Microsoft MVP - Exce
http://www.r-cor.co
 
...
...
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).
Click to expand...
...

This is a matter of opinion, and mine differs from yours on this point.

Data validation functionality in XL97 and XL2K allowed for securely controlled
dropdown list entry of 'locked' cells in protected worksheets. Only the values
in the dropdown list could be entered in such cells. *Unlocked* cells with any
form of validation can hold anything. There are two ways to bypass validation:
(1) enter a formula referring to something that initially matches an entry in
the dropdown list then change that something to an 'invalid' value; (2) paste as
text anything you please into such a cell. Excel's data validation is very
simplistic - it doesn't trap either of these.

So to me at least this is an example of foolish consistency winning out over
truly useful functionality. No doubt you like the fact that XLXP & later
functionality *requires* using *both* Change and Calculate event handlers to
trap invalid entries, not to mention the fun involved in maintaining referential
integrity between the worksheets and the event hadlers, but I really can't see
how this is an improvement.
 
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
Click to expand...
...

Try to enter foobar in such a cell, clear the nag message, then press in
sequence [Ctrl]+C, [Esc], [Ctrl]+V. Have fun with your support calls.
 
Back
Top