How to stop recursive calls on Click Event
How to stop recursive calls on Click Event
(OP)
I've developed a VB macro display a form to quickly hide and unhide columns in a large sheet. The value of the check box is toggled to TRUE or FALSE for each column. A FOR loop then shows or hides each column in the sheet.
The code for Hide/Unhiding a single column works fine,
' Hide/Unhide a Single column
Private Sub CheckBox1_Click()
CheckBox1.Value = Not CheckBox1.Value
End Sub
' Hide/Unhide a Group of columns
Private Sub cmbTotal_Click()
CheckBox1.Value = Not CheckBox1.Value
CheckBox2.Value = Not CheckBox2.Value
CheckBox3.Value = Not CheckBox3.Value
End Sub
But a problem occurs when I want to hide/unhide a bunch of similar columns as group with a command button.
As soon it executes the 1st code line in the cmbTotal command button it then calls the CheckBox1 routine, which in turn sets up a recursive call to the CheckBox1 routine. Result is that the macro now hangs!!!!
I believe its the Event CLICK that causes this problem as everytime the value of the checkbox changes VB sees it as an Event. Using the NOT statement is a very simple way of toggling the checkbox value, but it there a way to stop the Event CLICK recursive calls. Pls advise or easier way to do this.
Also, what code do I use to loop through all the checkboxes on the form using the FOR EACH statement instead of hardcoding each checkbox in the FOR loop.
Any help is greatly appreciated.
Krossview /OK
The code for Hide/Unhiding a single column works fine,
' Hide/Unhide a Single column
Private Sub CheckBox1_Click()
CheckBox1.Value = Not CheckBox1.Value
End Sub
' Hide/Unhide a Group of columns
Private Sub cmbTotal_Click()
CheckBox1.Value = Not CheckBox1.Value
CheckBox2.Value = Not CheckBox2.Value
CheckBox3.Value = Not CheckBox3.Value
End Sub
But a problem occurs when I want to hide/unhide a bunch of similar columns as group with a command button.
As soon it executes the 1st code line in the cmbTotal command button it then calls the CheckBox1 routine, which in turn sets up a recursive call to the CheckBox1 routine. Result is that the macro now hangs!!!!

I believe its the Event CLICK that causes this problem as everytime the value of the checkbox changes VB sees it as an Event. Using the NOT statement is a very simple way of toggling the checkbox value, but it there a way to stop the Event CLICK recursive calls. Pls advise or easier way to do this.
Also, what code do I use to loop through all the checkboxes on the form using the FOR EACH statement instead of hardcoding each checkbox in the FOR loop.
Any help is greatly appreciated.

Krossview /OK





RE: How to stop recursive calls on Click Event
Dim bFlag As Boolean
' Hide/Unhide a Single column
Private Sub CheckBox1_Click()
If bFlag = True Then Exit Sub
CheckBox1.Value = Not CheckBox1.Value
End Sub
' Hide/Unhide a Group of columns
Private Sub cmbTotal_Click()
bFlag = True
CheckBox1.Value = Not CheckBox1.Value
CheckBox2.Value = Not CheckBox2.Value
CheckBox3.Value = Not CheckBox3.Value
bFlag = False
End Sub
To loop through the CheckBoxes, you will need to loop through the controls.
Dim thisCtl As Control
Dim thisChk As CheckBox
For Each thisCtl In Me.Controls
If InStr(1, thisCtl.Name, "CheckBox") > 0 Then
thisCtl.Value = Not thisCtl.Value
End If
Next thisCtl
Hope that helps...
DimensionalSolutions@Core.com
While I welcome e-mail messages, please post all thread activity in these forums for the benefit of all members.
RE: How to stop recursive calls on Click Event
Also, the FOR loop works nice as I was stumped by that fact that you need to access check boxes indirectly as Control property. This does not seem intuitive to me.
You deserve a plume,
Krossview/OK