Counting Checkboxes in Userform

cjw1974 · Dec 17, 2009

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 · Dec 18, 2009

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 · Dec 18, 2009

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 · Dec 18, 2009

cjw1974 said:
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:

Option Explicit Private Sub CheckBox1_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox2_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox3_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox4_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox5_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox6_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox7_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox8_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox9_Click() Label1.Caption = RetVal End Sub Private Sub CheckBox10_Click() Label1.Caption = RetVal End Sub Private Sub UserForm_Initialize() Dim ctl As Control, i As Long For i = 1 To 10 With Me.Controls("Checkbox" & i) .Caption = "Checkbox" & i .Height = 18 .Width = 70 .Left = 6 .Top = 6 + ((i * 18) - 18) End With Next With Me .Height = 220 .Width = 6 + 110 + 6 With .Label1 .Left = 82 .Top = 6 .Width = 30 .Caption = vbNullString .BackColor = &HFFFFFF .SpecialEffect = fmSpecialEffectSunken .TextAlign = fmTextAlignCenter .Height = 18 End With End With End Sub Function RetVal() As Long Dim ctl As Control For Each ctl In Me.Controls If TypeName(ctl) = "CheckBox" Then If ctl Then RetVal = RetVal + Abs(ctl.Value) End If Next End Function 

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 · Dec 18, 2009

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 · Dec 19, 2009

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

Counting Checkboxes in Userform

cjw1974

New Member

Some videos you may like

Excel Facts

ProSolutions

New Member

ProSolutions

New Member

GTO

MrExcel MVP

Jaafar Tribak

Well-known Member

cjw1974

New Member

Similar threads

Watch MrExcel Video

Forum statistics

Share this page

Counting Checkboxes in Userform

cjw1974

New Member

Some videos you may like

Excel Facts

ProSolutions

New Member

ProSolutions

New Member

GTO

MrExcel MVP

Jaafar Tribak

Well-known Member

cjw1974

New Member

Similar threads

Watch MrExcel Video

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock