In order for drop down lists to work in a protected sheet, the cell with the list must be unprotected, yet this allows users to paste over the lists. Is there any way to prevent this?
Excel VBA Course - From Beginner to Expert
200+ Video Lessons
50+ Hours of Video
200+ Excel Guides
Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)
How do I prevent drop down lists from being pasted over?
0
Answers
0
Selected Answer
When the user attempts to paste a range of values into an area of a protected sheet that includes one unlocked cell (the validation drop-down) Excel will refuse to execute the command because of the locked cells included in the range. Therefore pasting would only be possible if the user targets that one cell specifically.
With that said, you might use VBA code to reset the validation based on certain workbook or worksheet events, for example, whenever the workbook is opened or when the worksheet is activated. Or you might specifically monitor the cell with the validation and take action when it is changed, for example, restore the validation when it has a value that isn't in the list and/or it doesn't have a validation list anymore.