Set value of checkbox on userform without triggering Click event

[Jonathan Brown]

I have a userform with several checkboxes on it. I have one "master"
checkbox that is supposed to be able to set all checkboxes true or false
based on its own state. So basically, if master checkbox is true then set
all checkboxes true, else if it's false then set all checkboxes false.

The annoying thing though is when it sets the value of each individual
checkbox it runs the code associated with each checkbox's click event. This
is slowing down my spreadsheet considerably.

Is it possible to set the value of a checkbox without Excel executing the
code associated with it's click event?

 

[ryguy7272]

Sub ClearCheckBoxes()
'Me.ListBox1.Clear...for ListBoxes
Dim ChkBox As Object

For Each ChkBox In UserForm1.CheckBoxes
ChkBox.Value = xlOff
Next ChkBox

End Sub

Sub EnterCheckBoxes()
'Me.ListBox1.Clear...for ListBoxes
Dim ChkBox As Object

For Each ChkBox In UserForm1.CheckBoxes
ChkBox.Value = xlOn
Next ChkBox

End Sub

HTH
Ryan---

 

[Howard31]

The code in the master checkbox (that sets the value of the others equal to
itself) should be put in the click event; the code for the other checkboxes
put in the MouseUp event this way clicking the master check wouldn't trigger
the code for the other check boxes.

I've try it and it works as expected unless if the code behind the other
check boxes is MsgBox then that will prevent the user from actually clicking
and changing the value of that check box.

 

[Jonathan Brown]

You guys are amazing. Seriously. What would I do without forums such as
this one?

 

[Jonathan Brown]

Ryan,

I'm having trouble using the Checkboxes collection of the Userform1. It
doesn't appear to be a valid collection. When I run the code I get an error
and it highlights that line.

I tried to modify it to look like below but it didn't work either.

Sub ClearCheckBoxes()
Dim ChkBox As Checkbox

For Each ChkBox In UserForm1.Controls
If ChkBox.ControlType = xlCheckbox Then
ChkBox.Value = xlOff
End If
Next ChkBox

End Sub

Is there some sort of a reference I need to include that I'm missing in
order for it to recognize the .CheckBoxes collection of the userform?

Thanks

 

[Dave Peterson]

Another way is to use a variable that acts like application.enableevents would
for worksheet/workbook events.

Inside your userform module:
Option Explicit
Dim BlkProc As Boolean
Private Sub CheckBox1_Change()
BlkProc = True
If Me.CheckBox1.Value = True Then
Me.CheckBox2.Value = True
Me.CheckBox3.Value = True
End If
BlkProc = False
End Sub
Private Sub CheckBox2_Change()
If BlkProc = True Then Exit Sub
'other code here
End Sub
Private Sub CheckBox3_Change()
If BlkProc = True Then Exit Sub
'other code here
End Sub

Checkbox1 was my master checkbox.

 

[Jonathan Brown]

Oh yeah, well that makes sense. Now, I'm wondering what
application.enableevents does exactly. Don't worry, you don't have to answer
that. I'll look it up. Thanks again!

 

[Dave Peterson]

Excel is monitoring certain events (changes/recalculations/sheet
activations/...) all the time.

You can use these events to your advantage by tying into event procedures.

For instance, if you wanted a date/time stamp each time a typing change was made
in column A, you could use a worksheet event like the one JE McGimpsey shares:
http://www.mcgimpsey.com/excel/timestamp.html

Chip Pearson has some instructions on events:
http://www.cpearson.com/excel/events.htm

David McRitchie has some notes, too:
http://www.mvps.org/dmcritchie/excel/event.htm

There are application events, too.
You can read more about application events at Chip Pearson's site:
http://www.cpearson.com/excel/AppEvent.aspx