data validation quirk

[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?

 

[Dave Peterson]

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.

 

[Debra Dalgleish]

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.

 

[Dave Peterson]

Oopsie.

I didn't remember it being different in xl97/xl2k.

 

[Dave Peterson]

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

 

[Debra Dalgleish]

(Not that I didn't trust your response, ma'am.)
Yeah, that's obvious. <g>

Anyway, this MSKB article has a bit more information on the previous
behaviour:

XL97: Error When Using Validation Drop-Down List Box
http://support.microsoft.com/default.aspx?id=157484

You made me fire up my work pc xl2k (again).

It really did change <vbg>.

 

[bwb]

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

 

[Dave Peterson]

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