data validation quirk

  • Thread starter Thread starter bwb
  • Start date Start date
B

bwb

I have created a worksheet in which users enter data in various fields
via dropdown lists. These dropdown lists were made using Excel's
standard data validation procedure: Data|Validation|Settings -->
Allow:List --> Source:=A range of cells named "Dates", on a separate
worksheet.

It was discovered that users could bypass this validation by pasting
data copied from another cell. We were happy to find that by
protecting the worksheet users could still use the dropdown lists to
enter values, but were unable to paste other values into these cells.

This was true for Excel 2000 on Windows 2000. But a couple of users
have tried running this file on Windows XP with Excel 2003. They are
unable to enter data via the dropdown lists unless the sheet protection
is removed.

Does anyone know why?
 
Are you sure that you locked the cell and protected the worksheet in xl2k?

I don't think that there has been a change in behavior.
 
I can't tell you why, but this behaviour was changed in Excel 2002. The
cell will have to be unlocked so users can use the data validation
dropdown in Excel 2002 or Excel 2003.
 
You made me fire up my work pc xl2k (again).

It really did change <vbg>.

(Not that I didn't trust your response, ma'am.)
 
Thank you all for the help. Here's a case where I wish Microsoft had
not fixed the bug.

I have a new design to do the validation by VB code triggered by
WorkSheet_Change events. So far so good.

bwb
 
Maybe a combobox from the Forms toolbar or from the Control toolbox toolbar
would have been an alternative.
 
Back
Top