Unprotect doesn't work with VBA code

Viorica000 

Thanks, that is very useful. I see what happens:

When the sheet is protected, Excel only copies/pastes the value, not the cell's formatting (including the data validation rule). So xCheck2 will always be the same as xCheck1.

On the plus side, the data validation of the target cell will not be overwritten.

But the downside is that you can paste a value that is not part of the data validation list (if any).

I don't see a solution, except altogether forbidding to paste to a cell with a data validation drop down:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim xCheck1 As Boolean
    If Target.Count > 1 Then
        Exit Sub
    End If
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    On Error Resume Next
    xCheck1 = Target.Validation.InCellDropdown
    On Error GoTo 0
    If xCheck1 Then
        MsgBox "It is not allowed to paste!"
        Application.Undo
    End If
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub