Worksheet_Change Event

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


--
safflenowPosted from http://www.pcreview.co.uk/ newsgroup access

I pasted your code into a test worksheet and made a small change...

Application.EnableEvents = False
Application.Undo

(I turned off .enableevents before doing the .undo.)

It seemed to work ok for me.

I don't see anything that will cause a circular reference in your code.


safflenow wrote:
>
> 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
>
> --
> safflenowPosted from http://www.pcreview.co.uk/ newsgroup access

--

Dave Peterson