Only Allow Paste of Values in Validation List

I have a validated drop down list in which I'm trying to prevent pasting values over. However, I want to allow the pasting of values that are included of the drop down list.

I've found codes that prevent pasting over validated cells, but not ones that have an exception to values within the list.

Example:

Suppose I have a worksheet titled Fruits with a validated drop down list in cell A1 that includes the two options "Apples" and "Bananas."

How can I prevent another user from pasting over the cell, with the exception of "Apples" and "Bananas" (in the exact format), while also preventing paste+special of values other than "Apples" and "Bananas."

Thank you in advance.

I have a validated drop down list in which I'm trying to prevent pasting values over. However, I want to allow the pasting of values that are included of the drop down list.

Example:

Suppose I have a worksheet titled Fruits with a validated drop down list in cell A1 that includes the two options "Apples" and "Bananas."

How can I prevent another user from pasting over the cell, with the exception of "Apples" and "Bananas" (in the exact format), while also preventing paste+special of values other than "Apples" and "Bananas."

Thank you in advance.

Add this code to the worksheet's code module.

• Right-click on the sheet tab
• Select View Code from the pop-up context menu
• Paste the code below in the VBA edit window
• There are two different IF statements highlighted in the code depending on the source of your Data Validation list; a range or a comma separated list. Use one or the other.

Please Login or Register  to view this content.

Thank you for the response AlphaFrog.

I am having trouble deciphering which part of the code to remove if my list is from a range. Would I use the code below (exactly as is) in order for it to work?

Please Login or Register  to view this content.

I tried using the aforementioned, but still get an error when I try to copy and paste "Apples" into the validated cell.

Thank you in advance for the clarification.

Thanks again for the quick reply AlphaFrog

The code I'm using is

Please Login or Register  to view this content.

When I copy paste (or paste special value) the exact word "Apples" over the data validated cell, the error message in the code ("Cannot paste over the data validation cell.") appears.

The formula for the data validations is

Formula:

Please Login or Register  to view this content.

("Fruits" being the range of "Apples" and "Bananas")

Your code looks correct.
It works for me using a named range called Fruits as the DV list range.
I can PasteSpecial: Value and it accepts "Apples"
If I PasteSpecial: Value it rejects "Oranges" and gives the error "Cannot paste values that are not within the list. "
If I just paste (not PasteSpecial: Value), it gives the error "Cannot paste over the data validation cell"

Example workbook: Special DV Lists.xlsm

Thanks AlphaFrog. That works perfectly.

What if I would like to use paste (not PasteSpecial: Value) for values in the DV list range also?

If you copy a cell, then using just paste will overwrite the Data Validation. I don't know how to get around that.

If you copy just text (not a cell), then you can use just paste.

Thanks for all the help AlphaFrog!

Cell level locking1.xlsm

Check the attachment.

B7 cell contains on drilldown to Apple & Bananas. Once you selected anything then, B7 cell gets locked.

To modify any data into B7 then, Press 'Unprotect Option' from Review tab and enter Password as 'abc'

You can modify the password or data reference by pressing Alt+F11


Regards,
Mohan.Y

Hey Mohan,

I'm trying lock a cell, without locking the worksheet, so the formula doesn't change.

Your code

Please Login or Register  to view this content.

locks the value, when the cell is changed, altogether.

For example:

If a cell has the formula

Formula:

Please Login or Register  to view this content.

I want it so that the user cannot change the formula, format, paste over, paste special over, etc.

Thanks in advance.

Replace this...
If Target.Address(0, 0) = "A1" Then

With something like this...
If Target.Count > 1 Then Exit Sub
If Not Intersect(Range("B1:B11,B13:B15,D1:D11,D13:D18"), Target) Is Nothing Then

Make a Named Range of B1:B11,B13:B15,D1:D11,D13:D18 and call it say MyDVLists. Then if you insert rows within the named range e.g. at row 2, then the Named range will automatically update to include the inserted rows. Reference that Named range in the code instead of the fixed cells.
If Target.Count > 1 Then Exit Sub
If Not Intersect(Range("MyDVLists"), Target) Is Nothing Then