6

I have one excel sheet which contains many dropdown columns. Lets say one column has Test1, Test2, and Test3 as dropdown option values. Cells under this column should not accept the values other than these 3. If I copy and paste 'XYZ' into these cells it should not accept. Can any one help me how to handle this?

7

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.

    Data Validation Location in Office Ribbon

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

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

    Data Validation Settings

  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:

enter image description here

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.

  • 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. – Charan Raju C R Feb 6 '15 at 6:38
  • @Charan I updated the answer with the VBA code required to deny the paste. This cannot be done without VBA unfortunately. – user387876 Feb 6 '15 at 19:32
0

A simpler solution would be to just disable pasting into the workbook entirely. I just found the solution for that here: http://www.vbaexpress.com/kb/getarticle.php?kb_id=373

  • A better answer would contain the answer and not just a link to it. – Enigma Jan 25 '16 at 19:49
  • 1
    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! – Excellll Jan 25 '16 at 22:31

Not the answer you're looking for? Browse other questions tagged or ask your own question.