Counting checkboxes with a true value
 

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

Counting checkboxes with a true value
 

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 Crawford wrote:
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

Counting checkboxes with a true value
 

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 Crawford wrote:
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

.