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.
I've tried including code to unprotect the worksheet but the function still doesn't pick up the invalid value.
Code:
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