Error Handling Help
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
Steph wrote:
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.
--
Steph
--
Dave Peterson
|