Worksheet_Change Event

[safflenow]

Hi,

In an attempt to protect my data validation from being deleted, I have written the following code. However, it seems to run into a 1004 error and a circular reference that Excel cannot list. Does anybody know where I am going wrong? More importantly, is this the right approach?


code:
--------------------------------------------------------------------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim DataRange As Range
Dim c As Range
Dim mycount As Integer

Worksheets("Sheet1").Unprotect "123456"

If Range("F12:G21").SpecialCells(xlCellTypeAllValidation).Count = 20 Then
GoTo 1:
Else
Application.Undo
Application.EnableEvents = False
MsgBox "Cannot delete data validation rules." & vbNewLine & _
"Please use Paste Special/Values Command.", vbCritical, "ERROR!"
Worksheets("Sheet1").Protect "123456"
Application.EnableEvents = True
Exit Sub
End If
1:
Application.EnableEvents = False
Set DataRange = Range("F12:G21")
mycount = 0

ActiveSheet.CircleInvalid

For Each c In DataRange
If Not c.Validation.Value Then
mycount = mycount + 1
End If
Next

If mycount = 0 Then Range("L10").Value = "0"
If mycount <> 0 Then
Range("L10").Value = "ERROR! THERE ARE INVALID ENTRIES. SEE HIGHLIGHTED CELLS."
End If
Worksheets("Sheet1").Protect "123456"
Application.EnableEvents = True
Exit Sub

End Sub
--------------------------------------------------------------------------------
Any help is appreciated