Locking Data Validation List

ardykav

Board Regular
Joined
Oct 18, 2015
Messages
166
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

6StringJazzer

Well-known Member
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Oct 18, 2015
Messages
166
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

Well-known Member
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
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

Well-known Member
Joined
Jan 27, 2010
Messages
846
Office Version
  1. 365
Platform
  1. Windows
If you can't use a combobox, does that also rule out having a userform pop up when the user selects the cell?
 

Watch MrExcel Video

Forum statistics

Threads
1,130,256
Messages
5,641,137
Members
417,195
Latest member
Vishal kumar

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
Top