Counting Checked Boxes

  • Thread starter Thread starter Greta
  • Start date Start date
G

Greta

Excel 2000....
Say I have a column of checkboxes, form control
checkboxes, some are checked, some are blank. How do I
sum to number of checked boxes in the column? Do I need
to use code? What code do I use?
Thanks,
Greta
 
Use the linked cells, assume you have links in cell Z2:Z20, to count all
checked cells
you can use a simple countif like

=COUNTIF(Z2:Z20,TRUE)

will count the checked cells
 
Thanks for the reply, but it didn't work.
What we're doing is asking people to check the box if they
participated in a program...no links. Is there a way to
do it?
Thanks,
Greta
 
If you right click the check box, then select format control, now there
should be a
reference box where you can put in a cell reference somewhere off on the
sheet where
it is not visible like IV1, now you can do that for all check boxes and link
them together
in a range so the 25th would link IV25, then use the formula and you'll get
the count, or run a macro
like this, stolen from Dave Peterson (it will count both types of checkboxes
in case you are using the control toolbox)

Option Explicit
Sub testme01()

Dim curWks As Worksheet

Dim OleObj As OLEObject
Dim cbx As CheckBox

Dim cbxFromForms As Long
Dim cbxFormsChkd As Long
Dim cbxFromCtrl As Long
Dim cbxCtrlChkd As Long

Set curWks = ActiveSheet

cbxFromForms = 0
cbxFormsChkd = 0
cbxFromCtrl = 0
cbxCtrlChkd = 0

cbxFromForms = curWks.CheckBoxes.Count

For Each cbx In curWks.CheckBoxes
If cbx.Value = xlOn Then
cbxFormsChkd = cbxFormsChkd + 1
End If
Next cbx

For Each OleObj In curWks.OLEObjects
If TypeOf OleObj.Object Is MSForms.CheckBox Then
cbxFromCtrl = cbxFromCtrl + 1
If OleObj.Object.Value = True Then
cbxCtrlChkd = cbxCtrlChkd + 1
End If
End If
Next OleObj


MsgBox "From forms: " & cbxFromForms & "--" & _
cbxFormsChkd & " are checked" & vbLf & _
"From Control: " & cbxFromCtrl & "--" _
& cbxCtrlChkd & " are checked" & vbLf & _
"total: " & cbxFromForms + cbxFromCtrl & "--" _
& cbxFormsChkd + cbxCtrlChkd & " are checked"

End Sub
 
Yep, that worked!!
Thanks so much,
Greta
-----Original Message-----
If you right click the check box, then select format control, now there
should be a
reference box where you can put in a cell reference somewhere off on the
sheet where
it is not visible like IV1, now you can do that for all check boxes and link
them together
in a range so the 25th would link IV25, then use the formula and you'll get
the count, or run a macro
like this, stolen from Dave Peterson (it will count both types of checkboxes
in case you are using the control toolbox)

Option Explicit
Sub testme01()

Dim curWks As Worksheet

Dim OleObj As OLEObject
Dim cbx As CheckBox

Dim cbxFromForms As Long
Dim cbxFormsChkd As Long
Dim cbxFromCtrl As Long
Dim cbxCtrlChkd As Long

Set curWks = ActiveSheet

cbxFromForms = 0
cbxFormsChkd = 0
cbxFromCtrl = 0
cbxCtrlChkd = 0

cbxFromForms = curWks.CheckBoxes.Count

For Each cbx In curWks.CheckBoxes
If cbx.Value = xlOn Then
cbxFormsChkd = cbxFormsChkd + 1
End If
Next cbx

For Each OleObj In curWks.OLEObjects
If TypeOf OleObj.Object Is MSForms.CheckBox Then
cbxFromCtrl = cbxFromCtrl + 1
If OleObj.Object.Value = True Then
cbxCtrlChkd = cbxCtrlChkd + 1
End If
End If
Next OleObj


MsgBox "From forms: " & cbxFromForms & "--" & _
cbxFormsChkd & " are checked" & vbLf & _
"From Control: " & cbxFromCtrl & "--" _
& cbxCtrlChkd & " are checked" & vbLf & _
"total: " & cbxFromForms + cbxFromCtrl & "--" _
& cbxFormsChkd + cbxCtrlChkd & " are checked"

End Sub


--

Regards,

Peo Sjoblom





.
Click to expand...
 
Back
Top