How do I prevent drop down lists from being pasted over?

0

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?

Answer
Discuss

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.

Discuss

Answer the Question

You must create an account to use the forum. Create an Account or Login