How to Count Ticked Checkboxes

[bullfrog83]

This may be a rather unusual question but how do you count how many checkboxes have been ticked? There's some logic that I want to apply to a form that has 3 unbound checkboxes but in order to apply it I need to be able to count how many have been ticked.

[munkee]

 
Sub countChecked()
Dim intTotalChecked As Integer
Dim ctl As Control
 
intTotalChecked = 0
For Each ctl In Me
   If ctl.ControlType = acCheckBox Then
     If ctl.Value = True Then
       intTotalChecked = intTotalChecked + 1
     End If
   End If
Next
MsgBox "Total Boxes Checked =" & intTotalChecked
End Sub
 
 

[OldBirdman]

True = -1 and False = 0, so @munkee's code lines 7-13 could be:

For Each ctl In Me 
   If ctl.ControlType = acCheckBox Then intTotalChecked = intTotalChecked - ctl.Value 
Next 

If you are going to use the count of checked checkboxes in your code, then it might be more useful as a function

 
Function CountChecked() as Integer
Dim intTotalChecked As Integer 
Dim ctl As Control 
 
intTotalChecked = 0 
For Each ctl In Me 
   If ctl.ControlType = acCheckBox Then intTotalChecked = intTotalChecked - ctl.Value 
Next 
 
CountChecked = intTotalChecked 
End Function 
 

Then the function could be used as:

If CountChecked <> 0 then MsgBox "No boxes are checked"
'or
MsgBox CountChecked & " Boxes are checked"

or:

Me.txtTotalCount = CountChecked

[bullfrog83]

I like your code revision and the idea of a function! I've implemented both. Thanks birdman!

[OldBirdman]

Glad I could help. @munkee did the work, I just added a couple of suggestions.