Help with code to prevent pasting over a data validation cell

[Rich_Atkins]

Hi I hope you can help,

I have a excel sheet which has several data validation drop down boxes, this is a shared folder and someone keep pasting over the cell removing the data validation.

I'm pretty new to VBA and with a help of a friend I have been using the code below, which prevents a blank cell being pasted over the drop down lists, but allows a cell with a different data validation to be pasted over it. Any help you can give would be greatly appreciated

Private Sub Worksheet_Change(ByVal Target As Range)
Dim xValue As String
Dim xCheck1 As String
Dim xCheck2 As String
If Target.Count > 1 Then
Exit Sub
End If
Application.EnableEvents = False
xValue = Target.Value
On Error Resume Next
xCheck1 = Target.Validation.InCellDropdown
On Error GoTo 0
Application.Undo
On Error Resume Next
xCheck2 = Target.Validation.InCellDropdown
On Error GoTo 0
If xCheck1 = xCheck2 Then
Target = xValue
Else
MsgBox "No pasting allowed!"
End If
Application.EnableEvents = True
End Sub

 

[Joe4]

Welcome to the Board!

You can disable all pasting with the code here: VBA Express : Excel - Disable Cut, Copy, Paste