Restrict paste into dropdown cells in Excel

Question

I have one Excel sheet which contains many drop-down columns.  Let's say one column has Test1, Test2, and Test3 as drop-down option values.  Cells under this column should not accept the values other than these three.  If I copy and paste 'XYZ' into these cells it should not accept.  Can anyone help me how to handle this?

Solution

Set up Data Validation

You will want to use the Data Validation feature in Excel to restrict cells to specific text. In your case: "Test1", "Test2", and "Test3"

1. Select the cells that should only accept the values "Test1", "Test2", and "Test3"

2. On the Data tab, in the Data Tools group, click the Data Validation button.

   

3. In the Allow: drop-down, select List

4. In the Source: field, enter Test1,Test2,Test3

   

5. Click OK to apply changes.

At this point, every cell you selected in Step 1 will have a drop-down list associated with it. Users of the spreadsheet will only be able to enter Test1, Test2, or Test3. If they try to enter anything else, they will get the following error message:

You Can customize the error message text, if desired, by clicking on the Error Alert tab in Step 4 from above.

Prevent Copy/Paste over Data Validated cells

This will work great, EXCEPT in the case where someone pastes content over the top of the cells. The only way to prevent that from happening is to write a macro that detects updates to the cells, determines if data validation is enabled on the cells, and then denies the paste if true.

1. Select all the cells that have data validation and name them DataValidationRange. You can name ranges of cells by typing the name to the left of the formula bar, where the Cell address is located.
2. In the code module for the worksheet (Sheet1, for example), add the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
    'Does the validation range still have validation?
    If HasValidation(Range("DataValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Error: You cannot paste data into these cells." & _
        "Please use the drop-down to enter data instead.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
    'Returns True if every cell in Range r uses Data Validation
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

Unfortunately, VBA is the only way to prevent someone from pasting over the cells. This has been an issue with Data Validation in Excel since the beginning. Hope it helps either way.

Comments

[Charan Raju C R]

Hi Tyler, Thank you for your help. It's working fine if I type or double click the cell and paste the invalid value. But it is not giving any error message if I click(single) on cell and paste.

[LaR]

@Charan I updated the answer with the VBA code required to deny the paste. This cannot be done without VBA unfortunately.

[Enigma]

A better answer would contain the answer and not just a link to it.

[Excellll]

Welcome to Super User! Could you please edit your answer to include the relevant information from the linked page? This makes sure your answer will be useful even if the linked page changes or disappears. Thanks!

[Scott - Слава Україні]

Burning down your house is a ‘‘simple’’ way to prevent termites.

[Scott - Слава Україні]

I tried your code (in Excel 2013), and the Application.Undo statement seems to cause a recursive call to Worksheet_Change.