Error Handling Help

[Steph]

I have the following click event tied to CheckBox1 in a userform:

Private Sub CheckBox1_Click()
If CheckBox1 = True Then
Selection.SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex = 6
ElseIf CheckBox1 = False Then
Selection.SpecialCells(xlCellTypeFormulas, 23).Interior.ColorIndex =
xlNone
End If
End Sub

I would like add the following features to be performed at the same time:

(1) display an error message if no formulas are found and
(2) turn the checkbox off (change the value to false).

When I try to set up the error handler, I seem to be getting caught in a
continuous loop. Can anyone suggest how to set up an efficient error
handling routine?

Thank you.

 

[Dave Peterson]

Option Explicit
Dim BlkProc As Boolean
Private Sub CheckBox1_Change()

Dim mySelection As Range

If BlkProc = True Then Exit Sub

Set mySelection = Nothing
On Error Resume Next
Set mySelection = Intersect(Selection, _
Selection.Cells.SpecialCells(xlCellTypeFormulas, 23))
On Error GoTo 0

If mySelection Is Nothing Then
msgbox "No formulas!"
BlkProc = True
Me.CheckBox1.Value = False
BlkProc = False
'maybe...
Selection.Interior.ColorIndex = xlNone
Else
If Me.CheckBox1.Value = True Then
mySelection.Interior.ColorIndex = 6
Else
mySelection.Interior.ColorIndex = xlNone
End If
End If

End Sub