I have a userform with 10 check boxes. I would like to maintain a tally of checkboxes on the userform so if I check boxes 1-5, the tally would instantly show the # 5 as the total number of checkboxes checked.
Greetings Chris,
You mention 10 boxes, but only checking 5. I presumed/guessed that if you had six of them checked, you'd want that returned as well...
By way of example:
Create a userform. Add 10 checkboxes to it, as well as one Label control. Allow the checkboxes and label to retain their default names, which will be like "CheckBox1", "CheckBox2", etc.
In the userform's module, paste this code:
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox1_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox2_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox3_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox4_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox5_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox6_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox7_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox8_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox9_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CheckBox10_Click()<br> Label1.Caption = RetVal<br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()<br><SPAN style="color:#00007F">Dim</SPAN> ctl <SPAN style="color:#00007F">As</SPAN> Control, i <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br> <br> <SPAN style="color:#00007F">For</SPAN> i = 1 <SPAN style="color:#00007F">To</SPAN> 10<br> <SPAN style="color:#00007F">With</SPAN> Me.Controls("Checkbox" & i)<br> .Caption = "Checkbox" & i<br> .Height = 18<br> .Width = 70<br> .Left = 6<br> .Top = 6 + ((i * 18) - 18)<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">Next</SPAN><br> <SPAN style="color:#00007F">With</SPAN> Me<br> .Height = 220<br> .Width = 6 + 110 + 6<br> <SPAN style="color:#00007F">With</SPAN> .Label1<br> .Left = 82<br> .Top = 6<br> .Width = 30<br> .Caption = vbNullString<br> .BackColor = &HFFFFFF<br> .SpecialEffect = fmSpecialEffectSunken<br> .TextAlign = fmTextAlignCenter<br> .Height = 18<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br> <br><SPAN style="color:#00007F">Function</SPAN> RetVal() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> ctl <SPAN style="color:#00007F">As</SPAN> Control<br> <br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ctl <SPAN style="color:#00007F">In</SPAN> Me.Controls<br> <SPAN style="color:#00007F">If</SPAN> TypeName(ctl) = "CheckBox" <SPAN style="color:#00007F">Then</SPAN><br> <SPAN style="color:#00007F">If</SPAN> ctl <SPAN style="color:#00007F">Then</SPAN> RetVal = RetVal + Abs(ctl.Value)<br> <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br> <SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Function</SPAN><br></FONT>
When you run the form, ea time a checkbox is ticked or un-ticked, the click event is called, and the RetVal function will add 1 for each ticked checkbox.
Hope that helps,
Mark