Locking Data Validation List

[ardykav]

I was wondering is it possible to lockdown a data validation list.

i.e Make a dropdown list available for cells A2:A100. Then protect the sheet so that A2:A100 can use the dropdown available but that list to be locked so no one can either take the data validation off or change the list available.

TIA

 

[6StringJazzer]

If you set the cell to unlocked, then protect the sheet, the user can select a value, but will not be able to update the Data Validation information in the cell. However, I have found they can also copy and paste in a value that is not in the list without getting an error.

Another option is to use a combobox instead of data validation in the cell.

 

[ardykav]

If you set the cell to unlocked, then protect the sheet, the user can select a value, but will not be able to update the Data Validation information in the cell. However, I have found they can also copy and paste in a value that is not in the list without getting an error.

Another option is to use a combobox instead of data validation in the cell.

Thanks for that, theres a few reasons why I cant use combo boxes. Really surprised that there is no straight forward solution for this and how people can copy and paste in the cells.

 

[6StringJazzer]

If you want people to be able to enter data in a cell, it has to be unlocked. If it's unlocked, they can also paste into it. People have complained about this for many years, mostly because it means that conditional formatting can be wiped out by the user. What Excel needs is a protection mode where the user can enter data but not paste, but there is nothing like that in sight.

 

[6StringJazzer]

If you can't use a combobox, does that also rule out having a userform pop up when the user selects the cell?