Error Handling Help
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 |
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 |
All times are GMT +1. The time now is 10:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com