Hello all -

I am running into similar problems to Pat from the following post:

http://groups.google.com/group/micro...tection&rnum=1

The code I have works when the sheet is unprotected, but does not when
the sheet is protected. I have even tried adding code to protect and
unprotect while running the macro, but it doesn't help. Same
problem. The "Sub Worksheet_Change" does get called but it always
returns "True" from HasValidation, instead of "False" like it does
when the worksheet is unproteced and I try to paste a blank cell over
a cell with data validation.

PLEASE HELP.

I have the following code:

Private Sub Worksheet_Change(ByVal Target As Range)

'Does the validation range still have validation?
Dim vRangeName As Variant
Dim bRangeCheck As Boolean
Dim i As Integer

i = 1

For i = 1 To 10

vRangeName = "ValidationRange" & i
bRangeCheck = RangeExists(vRangeName)

If bRangeCheck = True Then
'Unprotect sheet
ActiveSheet.Unprotect "password"

'Does the validation range still have validation?
If HasValidation(Range(vRangeName)) Then
'No Action
Else
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Your last operation was canceled, " & _
"as it would have deleted data validation rules.",
vbCritical, "DDS Invoice Message"
End If

'reprotect sheet
ActiveSheet.Protect "password"
End If
Next

End Sub

Public Function RangeExists(rangeName As Variant) As Boolean
Dim vRange As Variant
RangeExists = False

On Error GoTo BadRange
If rangeName < "" Then
vRange = Range(rangeName)
RangeExists = True 'will raise error where range does not
exist
'therefore to get here, it must
Exit Function
End If

BadRange:
End Function

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 If
End Function

Thanks-
KES