Check box counting

[jtfalk]

Good day,

I have a sheet with multiple check boxes. I would like checkbox1-checkbox5
to sum in cell M10.

For example if checkbox2 and checkbox4 are checked then the value in cell
M10 = 2

Also I am going to be copying this worksheet over and over so it hase to be
worksheet specific.

Any help would be appreciated

 

[Patrick Molloy]

do you really need code? If the linked cells are F1:F5 then you could put this

=COUNTIF(F1:F5,"TRUE")

into a cell which would sum the boxes that are checked for you.

 

[Jacob Skaria]

--One way is to link the checkbox to each cell and in cell M1 have a formula
to addup these cells..

--If you are looking for a macro...try the below

Sub Macro()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
For intTemp = 1 To 4
varTemp = varTemp + -(ws.OLEObjects("CheckBox" & intTemp).Object.Value)
Next
Range("M1") = varTemp
End Sub

If this post helps click Yes

 

[Jacob Skaria]

--You can link each checkbox to a cell and then have a formula in cell M1 to
add up the linked cells

--If you are looking for a macro

Sub Macro()
Dim ws As Worksheet
Set ws = Sheets("Sheet1")
For intTemp = 1 To 4
varTemp = varTemp + -(ws.OLEObjects("CheckBox" & intTemp).Object.Value)
Next
Range("M1") = varTemp
End Sub


If this post helps click Yes

 

[jtfalk]

Okay but how do you reference a checkbox?

Not through the name.
i tried COUNTIF(CheckBox1,TRUE)

 

[Rick Rothstein]

This macro will visit each worksheet and put the count you want for that
worksheet in M10 for any worksheets that have CheckBoxes on them...

Sub CountButtons()
Dim CB As OLEObject, WS As Worksheet, Total As Variant, Found As Boolean
For Each WS In Worksheets
Total = 0
Found = False
For Each CB In WS.OLEObjects
If CB.progID Like "*CheckBox*" Then
Found = True
If InStr(1, CB.Name, "checkbox", vbTextCompare) Then
If Right(CB.Name, 1) <= 5 Then
If CB.Object.Value Then Total = Total + 1
End If
End If
End If
Next
If Found Then WS.Range("M10").Value = Total
Next
End Sub

 

[JLGWhiz]

Put this code in the sheet code module:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
For i = 1 To 5
If Me.OLEObjects("CheckBox" & i).Object = "True" Then
x = x + 1
End If
Next
Range("M10") = x
End Sub


When you copy the sheet the code goes with it.

 

[Jacob Skaria]

In design mode right click each checkbox and from the properties window set
the linked cell to a cell F1....Do the same for all cells and then apply this
formula in M1

If this post helps click Yes