Not allow pasting in cell having data validation ( not working when sheet is protected)

[wouldbeca]

I had used below code given in some of the forum and it does the needful very well except when the sheet is locked and password protected. Can anyone give a workaround for the same?

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

How can I make it work, as I have to send the password protected sheet to user.

 

[Michael M]

I don't understand why you would have a Worksheet_Change event on a worksheet that is protected ???

 

[wouldbeca]

I don't understand why you would have a Worksheet_Change event on a worksheet that is protected ???

Hi. Thanks for the quick response. Actually, few of cells are protected ( formula cells), but in few cells, user input is required. That's why. I hope I made myself clear

 

[wouldbeca]

Waiting for a response. Please if somebody can help.