Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click event
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click event
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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jonathan Brown" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click event
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. -- A. Ch. Eirinberg "Jonathan Brown" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click eve
You guys are amazing. Seriously. What would I do without forums such as
this one? "Howard31" wrote: 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. -- A. Ch. Eirinberg "Jonathan Brown" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click eve
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 "ryguy7272" wrote: 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--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Jonathan Brown" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click event
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 wrote: 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? -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click eve
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" wrote: 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 wrote: 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? -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set value of checkbox on userform without triggering Click eve
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 Jonathan Brown wrote: 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" wrote: 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 wrote: 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? -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Click event for checkbox from Forms toolbar | Excel Discussion (Misc queries) | |||
userform label double-click goes to click event | Excel Programming | |||
Click Checkbox Event | Excel Programming | |||
Triggering click event of a menu in vba | Excel Programming | |||
Triggering click event of a menu in vba | Excel Programming |