data validation quirk

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.

bwb wrote:
>
> 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

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.

bwb wrote:
> 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?
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Oopsie.

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

Debra Dalgleish wrote:
>
> 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.
>
> bwb wrote:
> > 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?
> >
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html

--

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

Dave Peterson wrote:
>
> Oopsie.
>
> I didn't remember it being different in xl97/xl2k.
>
> Debra Dalgleish wrote:
> >
> > 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.
> >
> > bwb wrote:
> > > 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?
> > >
> >
> > --
> > Debra Dalgleish
> > Excel FAQ, Tips & Book List
> > http://www.contextures.com/tiptech.html
>
> --
>
> Dave Peterson

--

Dave Peterson

> (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

Dave Peterson wrote:
> You made me fire up my work pc xl2k (again).
>
> It really did change <vbg>.
>
>
> Dave Peterson wrote:
>
>>Oopsie.
>>
>>I didn't remember it being different in xl97/xl2k.
>>
>>Debra Dalgleish wrote:
>>
>>>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.
>>>
>>>bwb wrote:
>>>
>>>>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?
>>>>
>>>
>>>--
>>>Debra Dalgleish
>>>Excel FAQ, Tips & Book List
>>>http://www.contextures.com/tiptech.html
>>
>>--
>>
>>Dave Peterson
>
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

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.

bwb wrote:
>
> 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