Locking Data Validation List

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
172
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
If you can't use a combobox, does that also rule out having a userform pop up when the user selects the cell?
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top