Excel VBA checkbox click and change events the same?

Question

I have 5 checkboxes to set some options for a macro, one of them is a Select/Unselect All checkbox. I want to create something similar to what you have on web-based mailboxes when you select mails to delete or mark as read, etc. When I check the Select/Unselect All checkbox, I turn the rest of the checkboxes's values to true and viceversa when I uncheck it. That's ok.

The problem comes when I also want to validate that if everything is unchecked and one by one I check the other checkboxes, if in the end I check all, then the Select/Unselect All checkbox turns to checked. And viceversa, meaning that if eveything is checked and then I uncheck one of the four others, then I turn the "All" checkbox to false (unchecked).

But it seems that even when I just set the value, for example Option1Checkbox.value = True, in the SelectAllCheckbox_Click event, it triggers both the Option1Checkbox_Click and Option1Checkbox_Change events.

Shouldn't it just trigger the Change event since I'm not actually clicking that checkbox?

What happens is that I check the SelectAll, so it turns Option1 to checked, but by doing this, Option1 triggers also the click event so it unchecks it which triggers the click event again and then unchecks again the All checkbox which in the end leaves everything unchecked, like at the beginning. Hope this part is clear enough.

How can I avoid this behavior? How can I make sure only the Change event is triggered and not the Click event?

Has anyone ever had such an arrangement of checkboxes and had a similar problem? Or how did you manage to do this without the behavior I'm getting?

The checkboxes are not on a form but simply on a worksheet. They are ActiveX controls. And what I have is nothing complicated:

Private Sub SelectAll_Click()
    Option1Checkbox.Value = SelectAll.Value
    Option2Checkbox.Value = SelectAll.Value
    Option3Checkbox.Value = SelectAll.Value
    Option4Checkbox.Value = SelectAll.Value
End Sub

Then the options checkboxes click events look like this:

Private Sub Option1Checkbox_Click()
    If Option1Checkbox.Value = True And Option2Checkbox.Value = True And Option3Checkbox.Value = True And Option4Checkbox.Value = True Then
        SelectAll.Value = True
    Else
        SelectAll.Value = False
    End If
End Sub

It's quite simple, the biggest problem I see is the calls to the click events when the checkbox is not actually click.

Thanks for your help.

Answer 1

I would define a local variable (one defined outside the subs) and set/check that

Option Explicit
Dim ImChangingStuff As Boolean

Private Sub SelectAll_Click()
    ImChangingStuff = True
    Option1Checkbox.Value = SelectAll.Value
    Option2Checkbox.Value = SelectAll.Value
    Option3Checkbox.Value = SelectAll.Value
    Option4Checkbox.Value = SelectAll.Value
    ImChangingStuff = False
End Sub

then your click routines would look like this:

Private Sub Option1Checkbox_Click()
    If ImChangingStuff Then Exit Sub
    If Option1Checkbox.Value = True And Option2Checkbox.Value = True And Option3Checkbox.Value = True And Option4Checkbox.Value = True Then
        SelectAll.Value = True
    Else
        SelectAll.Value = False
    End If
End Sub

Answer 2

To solve this problem,

1. in your main userform (in this example it is called Mainform) add this code:

   Private Sub Mainform_Initialize() stateCheckbox = True End Sub

2. Create a module to store your global variables (eg. globalVariables), and add the following, which will hold the state of the checkbox:

   Public stateCheckbox as Boolean

3. In the _click event of your checkbox, wrap your code like this:

   if stateCheckbox = false then { whatever your code is } Else: stateCheckbox = false end if

Answer 3

I have had the same issue as described above. I recognized that each change of a Checkbox, no matter if by click or by changing Checkbox.Value, each time first fires the Checkbox_Change event and then the Checkbox_Click event , provided that any of these events are declared. I would thus recommend to use only one event for each checkbox, p.e. the Checkbox_Change event, and to check if the checkbox has been changed by click or by declaration at the beginning of the Sub. This can be done by comparing the ActiveControl.Name with the Checkbox.Name:

Private Sub CheckBox1_Change()
On Error Goto Err:
If ActiveControl.Name = CheckBox1.Name Then 
    On Error Goto 0        
    'Commands for click
    Exit Sub
Else
    On Error Goto 0 
    'Commands for change from within the Userform
    Exit Sub
Err:On Error Goto 0 
    'Commands for change from outside the Userform
End Sub