Avoid Copy paste over Data Validation cell

[Bryant]

For some of my cells I have drop-down menus. It is such that the user can select value from the drop-down list but when a user types anything that is not in the list an error message pops up via data validation error alert. I works fine when the user copy paste a cell with different drop list into a cell by overwriting the existing validation. I cannot totally lock the cells. What to do next?

[Vandam]

Your issue seems to be an interesting one. Try the below VBA code. If you see the syntax in that, its very new and tricky but it will do the job for you.

Private Sub Worksheet_Change(ByVal Target As Range)
    
    If HasValidation(Range(ActiveCell.Address)) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", vbCritical
    End If
End Sub

Private Function HasValidation(r) As Boolean
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

[Jacques25]

The below VBA is older one but its very easy and convenient to use. You can easily understand after looking at the code.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Unprotect 'only if your sheet is protected
If Selection.SpecialCells(xlCellTypeAllValidation).Count <> Selection.Cells.Count Then
        Application.EnableEvents = False
        Application.Undo
        Application.EnableEvents = True
        MsgBox ("THE OPERATION YOU JUST PERFORMED WOULD HAVE ERASED DATA VAIDATION, THEREFORE IT WAS CANCELLED")
        ActiveSheet.Protect 'only if you want protect your sheet   
Else
End If
End Sub

[Author]

Just keep in mind that the below code should be utilized in the indication where there is data validation, its important. These will avoid error message due to data validation. The data validation is in the Columns that are mentioned in the code.

Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Column
 Case 1,2,3
    ActiveSheet.Unprotect 'only if your sheet is protected
    If Selection.SpecialCells(xlCellTypeAllValidation).Count <> Selection.Cells.Count Then
      Application.EnableEvents = False
      Application.Undo
      Application.EnableEvents = True
      MsgBox ("THE OPERATION YOU JUST PERFORMED WOULD HAVE ERASED DATA VAIDATION, THEREFORE IT WAS CANCELLED")
   Else
   End If
   ActiveSheet.Protect 'only if you want protect your sheet 
End Select
End Sub

[Dwarner]

I am very much familiar to such kind of issues because I have personally faced similar one. In Office/Excel 2010 the default code prevents us to paste data into the cells that has data validation. You must also be getting stop error multiple times, to stop the message appearing you have to continuous press Escape key. Not only that, even double click on a cell causes the same stop message.

[Ronchi]

The following VBA uses an event procedure. You can have look at the code and make necessary changes in it as per your needs then use it.

Private Sub Worksheet_Change(ByVal Target As Range)
    If HasValidation(Range("ValidationRange")) Then
        Exit Sub
    Else
        Application.Undo
        MsgBox "Your last operation was canceled." & _
        "It would have deleted data validation rules.", 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

[Steyn]

You can target the cells directly to disallow copy, paste, drag & drop changes in validation cells. Below it the procedure to do that:

Private Sub Workbook_WindowActivate(ByVal Wn As Window)
With Application
.CutCopyMode = False
.CellDragAndDrop = False
.OnKey "^c", ""
End With
End Sub

Private Sub Workbook_Activate()
With Application
.CutCopyMode = False
.CellDragAndDrop = False
.OnKey "^c", ""
End With
End Sub

Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Cancel = True
MsgBox "Right click menu deactivated." & vbCrLf & _
"Cannot cut, copy, paste, or ''drag & drop''.", 16, "For this file:"
End Sub

[Shreevats]

You can also do that by using a different unique technique such as disallowing the keys of copy, cut and paste. The VBA for that is:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Application
.OnKey "^c", ""
.CutCopyMode = False
.CellDragAndDrop = False
End With
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Application.CutCopyMode = False
End Sub

Private Sub Workbook_Deactivate()
With Application
.CellDragAndDrop = True
.OnKey "^c"
.CutCopyMode = False
End With
End Sub

Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
With Application
.CellDragAndDrop = True
.OnKey "^c"
.CutCopyMode = False
End With
End Sub