Counting Checkboxes in Userform

cjw1974

New Member
Joined
Dec 14, 2009
Messages
4
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
 

Some videos you may like

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20
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:
Code:
Private sub ChkBoxCount()
dim x as integer
dim chkd as boolean
 
x=0
If frm>>>>.chk1.checked then
x = x +1
If
 

ProSolutions

New Member
Joined
Dec 15, 2009
Messages
20
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

MrExcel MVP
Joined
Dec 9, 2008
Messages
6,154
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

Well-known Member
Joined
Dec 5, 2002
Messages
8,118
Office Version
  1. 2016
Platform
  1. Windows
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 :

Code:
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

New Member
Joined
Dec 14, 2009
Messages
4
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,863
Messages
5,598,504
Members
414,245
Latest member
Major Aly

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top