Counting checkboxes with a true value

  • Thread starter Thread starter Antje Crawford
  • Start date Start date
A

Antje Crawford

Hello,
I have four checkboxes on a worksheet. I would like to
have a macro that anytime a checkmark is placed in one of
the checkboxes to go through those checkboxes, validate
for their value (true or false), and count the ones that
are true. It then puts that count into a cell on the same
worksheet. I have assigned the same group name to all four
checkboxes.
Can anyone help please?
All suggestions/solutions are greatly appreciated.
TIA.
BR, Antje Crawford
 
Hi Antje,

Something like this should work:

Private Sub chk1_Click()
CalcCount
End Sub

Private Sub chk2_Click()
CalcCount
End Sub

Private Sub chk3_Click()
CalcCount
End Sub

Private Sub chk4_Click()
CalcCount
End Sub

Private Sub CalcCount()
Dim ctl As OLEObject
Dim n As Integer

For Each ctl In OLEObjects
If TypeOf ctl.Object Is MSForms.CheckBox Then
If ctl.Object.Value And _
ctl.Object.GroupName = "Test" Then n = n + 1
End If
Next ctl

Range("A1").Value = n
End Sub

This assumes your GroupName is "Test". Just put this code behind the
worksheet containing the checkboxes (right-click sheet tab | View Code).

Alternatively, you could set the LinkedCell property of each checkbox to a
different cell and count up the TRUE values with a COUNTIF formula. That
would require no VBA code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]
 
Hi,

You can use LinkedCell properties of checkboxes. Just assign a cell for each checkbox (must be continious, like A1 to A4 checkbox1.linkedcell =A1 .... checkbox4.linkedcell=A4).
and use =COUNTIF(A1:A4,TRUE) function in the cell which you want to put the counts of trues.


--
Regards

Haldun Alay

To e-mail me, please remove AT and DOT from my e-mail address.



"Antje Crawford" <[email protected]>, iletide sunu yazdi Hello,
I have four checkboxes on a worksheet. I would like to
have a macro that anytime a checkmark is placed in one of
the checkboxes to go through those checkboxes, validate
for their value (true or false), and count the ones that
are true. It then puts that count into a cell on the same
worksheet. I have assigned the same group name to all four
checkboxes.
Can anyone help please?
All suggestions/solutions are greatly appreciated.
TIA.
BR, Antje Crawford
 
Jake,
it works like a charm. Thanks alot.
BR,
Antje Crawford
-----Original Message-----
Hi Antje,

Something like this should work:

Private Sub chk1_Click()
CalcCount
End Sub

Private Sub chk2_Click()
CalcCount
End Sub

Private Sub chk3_Click()
CalcCount
End Sub

Private Sub chk4_Click()
CalcCount
End Sub

Private Sub CalcCount()
Dim ctl As OLEObject
Dim n As Integer

For Each ctl In OLEObjects
If TypeOf ctl.Object Is MSForms.CheckBox Then
If ctl.Object.Value And _
ctl.Object.GroupName = "Test" Then n = n + 1
End If
Next ctl

Range("A1").Value = n
End Sub

This assumes your GroupName is "Test". Just put this code behind the
worksheet containing the checkboxes (right-click sheet tab | View Code).

Alternatively, you could set the LinkedCell property of each checkbox to a
different cell and count up the TRUE values with a COUNTIF formula. That
would require no VBA code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Antje said:
Hello,
I have four checkboxes on a worksheet. I would like to
have a macro that anytime a checkmark is placed in one of
the checkboxes to go through those checkboxes, validate
for their value (true or false), and count the ones that
are true. It then puts that count into a cell on the same
worksheet. I have assigned the same group name to all four
checkboxes.
Can anyone help please?
All suggestions/solutions are greatly appreciated.
TIA.
BR, Antje Crawford
Click to expand...

.
Click to expand...
 
Back
Top