How to stop recursive calls on Click Event

[krossview]

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

 

[dsi]

You can add a global variable to flag weather or not the sub is making the changes.

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.

 

[krossview]

Thanks dsi, this works like a charm. I was looking for ways the disable the Click EVENT, but this trick works very nicely.
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, and thanks again for your great help.
Krossview/OK