Data Validation Macro stops working when worksheet is protected

[keeferino]

Hey guys, I'm using a macro to ensure that data validation hasn't been pasted over and everything is working fine, until I protect the workbook. Only the first row of data is protected and user are allowed to make almost any modification aside from inserting new columns. The hang up is the HasValidation function. Once the workbook is protected the function always returns 0 errors even if i have pasted in an invalid value. I've been searching all morning and can't come up with an answer.

I've tried including code to unprotect the worksheet but the function still doesn't pick up the invalid value.

Private Sub Worksheet_Change(ByVal Target As Range)
[COLOR=#0000ff]    'Does the validation range still have validation?[/COLOR]
    If HasValidation(target.column) 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
[COLOR=#0000ff]'   Returns True if every cell in Range r uses Data Validation[/COLOR]
    On Error Resume Next
    x = r.Validation.Type
    If Err.Number = 0 Then HasValidation = True Else HasValidation = False
End Function

 

[keeferino]

Guess that will have to work.