Drop Down List Protection from Changes

[kfutbol]

When protecting a sheet, I know you can prevent changes to cell content but
how can you prevent a change in a drop down list? Once I protect the sheet,
I can still change a drop down list. How can I prevent this? Thank you!

[Springbok]

Hi,

Is the drop down list part of validation / combo box? If it is is it linked
to a list in an array in the same workbook? You can protect it by locking
the cells that make up the list array. Then apply worksheet protection.

Cheers,
Jon

"kfutbol" wrote:

> When protecting a sheet, I know you can prevent changes to cell content but
> how can you prevent a change in a drop down list? Once I protect the sheet,
> I can still change a drop down list. How can I prevent this? Thank you!

[kfutbol]

It is a validation and uses a list on the same sheet
(Data->Validation->Lists->Select Cells). I highlighted the list and selected
Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
still change the drop down list. What am I doing wrong?? Thanks.

"Springbok" wrote:

> Hi,
>
> Is the drop down list part of validation / combo box? If it is is it linked
> to a list in an array in the same workbook? You can protect it by locking
> the cells that make up the list array. Then apply worksheet protection.
>
> Cheers,
> Jon
>
> "kfutbol" wrote:
>
> > When protecting a sheet, I know you can prevent changes to cell content but
> > how can you prevent a change in a drop down list? Once I protect the sheet,
> > I can still change a drop down list. How can I prevent this? Thank you!

[Springbok]

Not sure what you mean by "change". Do you mean that you can change the list
(i.e. delete existing values, insert, modify etc...)?

Or do you mean that you can still select the item from the drop down list
and you want to restrict users from using the list?

If the first, then I don't understand why because when the list is 'locked'
and the sheet protected, I don't seem to be able to amend it.

If the second, then make sure you are locking the cells that the validation
is applied to.

Let me know what happens.
Cheers,
Jon

"kfutbol" wrote:

> It is a validation and uses a list on the same sheet
> (Data->Validation->Lists->Select Cells). I highlighted the list and selected
> Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
> still change the drop down list. What am I doing wrong?? Thanks.
>
> "Springbok" wrote:
>
> > Hi,
> >
> > Is the drop down list part of validation / combo box? If it is is it linked
> > to a list in an array in the same workbook? You can protect it by locking
> > the cells that make up the list array. Then apply worksheet protection.
> >
> > Cheers,
> > Jon
> >
> > "kfutbol" wrote:
> >
> > > When protecting a sheet, I know you can prevent changes to cell content but
> > > how can you prevent a change in a drop down list? Once I protect the sheet,
> > > I can still change a drop down list. How can I prevent this? Thank you!

[Debra Dalgleish]

In Excel 2000 and earlier versions, you can change the selection in a
data validation dropdown, if the list is from a range on the worksheet.

If the list is typed in the data validation dialog box, the selection
can't be changed.

In Excel 2002 and later versions, neither type of dropdown list can be
changed if the cell is locked and the sheet is protected.

This MSKB article has information on the previous behaviour:

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


kfutbol wrote:
> It is a validation and uses a list on the same sheet
> (Data->Validation->Lists->Select Cells). I highlighted the list and selected
> Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
> still change the drop down list. What am I doing wrong?? Thanks.
>
> "Springbok" wrote:
>
>
>>Hi,
>>
>>Is the drop down list part of validation / combo box? If it is is it linked
>>to a list in an array in the same workbook? You can protect it by locking
>>the cells that make up the list array. Then apply worksheet protection.
>>
>>Cheers,
>>Jon
>>
>>"kfutbol" wrote:
>>
>>
>>>When protecting a sheet, I know you can prevent changes to cell content but
>>>how can you prevent a change in a drop down list? Once I protect the sheet,
>>>I can still change a drop down list. How can I prevent this? Thank you!
>>


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

[kfutbol]

Can you clarify what you mean by typing the list information in the data
vaildation dialog box? Thank you!

"Debra Dalgleish" wrote:

> In Excel 2000 and earlier versions, you can change the selection in a
> data validation dropdown, if the list is from a range on the worksheet.
>
> If the list is typed in the data validation dialog box, the selection
> can't be changed.
>
> In Excel 2002 and later versions, neither type of dropdown list can be
> changed if the cell is locked and the sheet is protected.
>
> This MSKB article has information on the previous behaviour:
>
> XL97: Error When Using Validation Drop-Down List Box
> http://support.microsoft.com/default.aspx?id=157484
>
>
> kfutbol wrote:
> > It is a validation and uses a list on the same sheet
> > (Data->Validation->Lists->Select Cells). I highlighted the list and selected
> > Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
> > still change the drop down list. What am I doing wrong?? Thanks.
> >
> > "Springbok" wrote:
> >
> >
> >>Hi,
> >>
> >>Is the drop down list part of validation / combo box? If it is is it linked
> >>to a list in an array in the same workbook? You can protect it by locking
> >>the cells that make up the list array. Then apply worksheet protection.
> >>
> >>Cheers,
> >>Jon
> >>
> >>"kfutbol" wrote:
> >>
> >>
> >>>When protecting a sheet, I know you can prevent changes to cell content but
> >>>how can you prevent a change in a drop down list? Once I protect the sheet,
> >>>I can still change a drop down list. How can I prevent this? Thank you!
> >>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[kfutbol]

The 2nd option: I do not want others to change the choice I placed in the
drop down list. I highlighted the cells that contain teh list and they are
already protected and then when I protect, I can still change the drop-down
list from one choice to the other. Any ideas?

"Springbok" wrote:

> Not sure what you mean by "change". Do you mean that you can change the list
> (i.e. delete existing values, insert, modify etc...)?
>
> Or do you mean that you can still select the item from the drop down list
> and you want to restrict users from using the list?
>
> If the first, then I don't understand why because when the list is 'locked'
> and the sheet protected, I don't seem to be able to amend it.
>
> If the second, then make sure you are locking the cells that the validation
> is applied to.
>
> Let me know what happens.
> Cheers,
> Jon
>
> "kfutbol" wrote:
>
> > It is a validation and uses a list on the same sheet
> > (Data->Validation->Lists->Select Cells). I highlighted the list and selected
> > Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
> > still change the drop down list. What am I doing wrong?? Thanks.
> >
> > "Springbok" wrote:
> >
> > > Hi,
> > >
> > > Is the drop down list part of validation / combo box? If it is is it linked
> > > to a list in an array in the same workbook? You can protect it by locking
> > > the cells that make up the list array. Then apply worksheet protection.
> > >
> > > Cheers,
> > > Jon
> > >
> > > "kfutbol" wrote:
> > >
> > > > When protecting a sheet, I know you can prevent changes to cell content but
> > > > how can you prevent a change in a drop down list? Once I protect the sheet,
> > > > I can still change a drop down list. How can I prevent this? Thank you!

[kfutbol]

Got it!! Thank you very much! It will take a lot longer to type it in, but
it'll be worth it.

"Debra Dalgleish" wrote:

> In Excel 2000 and earlier versions, you can change the selection in a
> data validation dropdown, if the list is from a range on the worksheet.
>
> If the list is typed in the data validation dialog box, the selection
> can't be changed.
>
> In Excel 2002 and later versions, neither type of dropdown list can be
> changed if the cell is locked and the sheet is protected.
>
> This MSKB article has information on the previous behaviour:
>
> XL97: Error When Using Validation Drop-Down List Box
> http://support.microsoft.com/default.aspx?id=157484
>
>
> kfutbol wrote:
> > It is a validation and uses a list on the same sheet
> > (Data->Validation->Lists->Select Cells). I highlighted the list and selected
> > Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
> > still change the drop down list. What am I doing wrong?? Thanks.
> >
> > "Springbok" wrote:
> >
> >
> >>Hi,
> >>
> >>Is the drop down list part of validation / combo box? If it is is it linked
> >>to a list in an array in the same workbook? You can protect it by locking
> >>the cells that make up the list array. Then apply worksheet protection.
> >>
> >>Cheers,
> >>Jon
> >>
> >>"kfutbol" wrote:
> >>
> >>
> >>>When protecting a sheet, I know you can prevent changes to cell content but
> >>>how can you prevent a change in a drop down list? Once I protect the sheet,
> >>>I can still change a drop down list. How can I prevent this? Thank you!
> >>
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[kfutbol]

Thanks again for all your help! I finally got it to work. I just have to
type int eh list info into teh Range box as opposed to referring to a list of
cells. Take care~

"Springbok" wrote:

> Not sure what you mean by "change". Do you mean that you can change the list
> (i.e. delete existing values, insert, modify etc...)?
>
> Or do you mean that you can still select the item from the drop down list
> and you want to restrict users from using the list?
>
> If the first, then I don't understand why because when the list is 'locked'
> and the sheet protected, I don't seem to be able to amend it.
>
> If the second, then make sure you are locking the cells that the validation
> is applied to.
>
> Let me know what happens.
> Cheers,
> Jon
>
> "kfutbol" wrote:
>
> > It is a validation and uses a list on the same sheet
> > (Data->Validation->Lists->Select Cells). I highlighted the list and selected
> > Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
> > still change the drop down list. What am I doing wrong?? Thanks.
> >
> > "Springbok" wrote:
> >
> > > Hi,
> > >
> > > Is the drop down list part of validation / combo box? If it is is it linked
> > > to a list in an array in the same workbook? You can protect it by locking
> > > the cells that make up the list array. Then apply worksheet protection.
> > >
> > > Cheers,
> > > Jon
> > >
> > > "kfutbol" wrote:
> > >
> > > > When protecting a sheet, I know you can prevent changes to cell content but
> > > > how can you prevent a change in a drop down list? Once I protect the sheet,
> > > > I can still change a drop down list. How can I prevent this? Thank you!

[Debra Dalgleish]

You're welcome! Thanks for letting me know that you figured out how to
type the list in the data validation box, and got it working.

kfutbol wrote:
> Thanks again for all your help! I finally got it to work. I just have to
> type int eh list info into teh Range box as opposed to referring to a list of
> cells. Take care~
>
> "Springbok" wrote:
>
>
>>Not sure what you mean by "change". Do you mean that you can change the list
>>(i.e. delete existing values, insert, modify etc...)?
>>
>>Or do you mean that you can still select the item from the drop down list
>>and you want to restrict users from using the list?
>>
>>If the first, then I don't understand why because when the list is 'locked'
>>and the sheet protected, I don't seem to be able to amend it.
>>
>>If the second, then make sure you are locking the cells that the validation
>>is applied to.
>>
>>Let me know what happens.
>>Cheers,
>>Jon
>>
>>"kfutbol" wrote:
>>
>>
>>>It is a validation and uses a list on the same sheet
>>>(Data->Validation->Lists->Select Cells). I highlighted the list and selected
>>>Format->Cells->Protection Tab->Lock and then I protected the sheet, but I can
>>>still change the drop down list. What am I doing wrong?? Thanks.
>>>
>>>"Springbok" wrote:
>>>
>>>
>>>>Hi,
>>>>
>>>>Is the drop down list part of validation / combo box? If it is is it linked
>>>>to a list in an array in the same workbook? You can protect it by locking
>>>>the cells that make up the list array. Then apply worksheet protection.
>>>>
>>>>Cheers,
>>>>Jon
>>>>
>>>>"kfutbol" wrote:
>>>>
>>>>
>>>>>When protecting a sheet, I know you can prevent changes to cell content but
>>>>>how can you prevent a change in a drop down list? Once I protect the sheet,
>>>>>I can still change a drop down list. How can I prevent this? Thank you!
>>>>


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