Counting Checkboxes in Userform

[cjw1974]

Hello everyone, I am working on a project and in the final stages. I am new to Excel VBA, so please understand.

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.

I am sorry I cannot offer any code, but I have no idea how to have a tally display on the userform in real-time.

I'd appreciate any help anyone can offer. Thanks!!

-Chris

 

[ProSolutions]

You could do a Looping structure to see which ones are checked and dim a variable to hold count and after you determine how many are checked display the VALUE of the variable in a text box or some kind of output object.

some thing like this perhaps:

Private sub ChkBoxCount()
dim x as integer
dim chkd as boolean
 
x=0
If frm>>>>.chk1.checked then
x = x +1
If

 

[ProSolutions]

Sorry about that it kicked me out. If you continue with nested IF's and end up with the count (variable X) as 3 then use the variable to assign to a text box on the form and use its value property this should give you the count of check boxes that are checked on the form.

 

[GTO]

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

 

[Jaafar Tribak]

WORKBOOK DEMO.

Here is another approach that hooks any number of checkboxes and updates the tally label dynamically at real time .

The neat thing about this is that it hooks any number of checkboxes and without using a seperate Class module. All self-contained in the UserForm module.

Add the checkboxes to the userform and a Label Control with its default name ie : Label1

Code In the UserForm Module :

Option Explicit
 
Public WithEvents chkbEvents As MSForms.CheckBox
 
Private Sub UserForm_Initialize()
 
    If UserForms.Count > 1 Then Exit Sub
 
    Call HookCheckBoxes
        '=================================
 
        '\\\\ Place any other existing code here....
 
        '=================================
 
End Sub
 
Private Sub UserForm_Terminate()
 
        '=================================
 
        '\\\\ Place any other existing code here....
 
        '=================================
    Call UnloadForms
 
End Sub
 
Private Sub chkbEvents_Click()
 
    Dim oCtl As Control
    Dim lTotal As Long
 
    For Each oCtl In UserForms(0).Controls
        If TypeOf oCtl Is MSForms.CheckBox Then
            If oCtl.Value = True Then lTotal = lTotal + 1
            UserForms(0).Label1.Caption = "Total Boxes checked:  " _
            & (lTotal)
        End If
    Next
 
End Sub
 
Private Sub HookCheckBoxes()
 
    Dim oCtl As Control
    Dim lTotal As Long
 
    Label1.Caption = "Total Boxes checked: " & (lTotal)
 
    For Each oCtl In Me.Controls
        If TypeOf oCtl Is MSForms.CheckBox Then
            If oCtl.Value = True Then lTotal = lTotal + 1
            CallByName UserForms.Add(Me.Name), "chkbEvents", VbSet, oCtl
        End If
    Next
 
    Label1.Caption = "Total Boxes checked:  " & (lTotal)
 
End Sub
 
Private Sub UnloadForms()
 
    End
 
End Sub

Regards.

 

[cjw1974]

Thank you all so much. I was able to implement the code and am working out the remaining bugs. Thank you for your time.

-Chris