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.

+1 I just encountered the same problem designing a user form in Excel. — Excellll
– Excellll, Commented Apr 4, 2014 at 15:38
I tried disabling events with Application.EnableEvents = False before changing the checkbox value, but it still triggered the Click event. Not sure why....? — Excellll
– Excellll, Commented Apr 4, 2014 at 16:39

SeanC · Accepted Answer · 2013-08-08 17:37:24Z

2

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

answered Aug 8, 2013 at 17:37

SeanC

16k5 gold badges48 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mikebarrera Over a year ago

Thank you guys for your suggestions. The problem is still the triggering of the Click event even when you don't click it yourself. So the ImChangingStuff doesn't seem to solve the problem because in the Option1Checkbox_Click event, let's say ImChangingStuff is false (cause you didn't click SelectAll, so it continues and when line SelectAll.Value = True, or the false one, evaluates, then it triggers the SelectAll_Click event even when I didn't actually click on it.

mikebarrera Over a year ago

What I don't understand is why the Click and Change events are always both triggered. I thought the Change event but not the Click event would be triggered when I change the checkbox value. And if I actually click the checkbox then I understand both are triggered.

Elbert Villarreal · Accepted Answer · 2016-02-10 22:14:37Z

2

To solve this problem,

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

Private Sub Mainform_Initialize() stateCheckbox = True End Sub
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
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

edited Feb 10, 2016 at 22:14

Elbert Villarreal

1,7261 gold badge11 silver badges23 bronze badges

answered Feb 10, 2016 at 21:37

user5910731

211 bronze badge

Comments

DaveD · Accepted Answer · 2016-07-20 09:20:00Z

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

RestitutorOrbis · Accepted Answer · 2013-08-08 12:01:08Z

I would write a sub checking if Option1 to Option4 are all checked in an If statement. In a second if statemement, I'd check to see if they are all false:

Sub Are_O1_to_O4_All_Checked ()
  If Option1Checkbox.Value = True And _ 
     Option2Checkbox.Value = True And _
     Option3Checkbox.Value = True And _
     Option4Checkbox.Value = True Then
        Option5Checkbox.Value  = True      ' select/unselect checkbox
  End If

  If Option1Checkbox.Value = False And _ 
     Option2Checkbox.Value = False And _
     Option3Checkbox.Value = False And _
     Option4Checkbox.Value =  False Then
        Option5Checkbox.Value  = False      ' select/unselect checkbox
  End If

End Sub

Then I would Call this sub at the end of each OnClick event for the check boxes.

Collectives™ on Stack Overflow

Excel VBA checkbox click and change events the same?

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related