Forms Checkbox

[Guest]

I have a list of names with checkboxes next to them. I want to add up all
the checkboxes that are ticked. Approx 100 lines.

I have not linked them already to a cell and don't want to change the code
for every cell individually.

Is there a quick way to do this?

Thanks very much

 

[Dave Peterson]

You could use a macro to add the linked cell.

This code assumes that you used the checkbox from the Forms toolbar--not from
the Control Toolbox toolbar and it links the cell directly below the
checkbox--and hides the value in that cell using a custom format:

Option Explicit
Sub testme()
Dim CBX As CheckBox
With Worksheets("sheet1")
For Each CBX In .CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX
End With
End Sub

Then you could use:
=countif(yourrange,true)
to count the number checked.

=======

This code assumes you used the checkbox from the Control toolbox toolbar--not
the Forms toolbar:

Option Explicit
Sub testme2()
Dim OLEObj As OLEObject
With Worksheets("sheet1")
For Each OLEObj In .OLEObjects
With OLEObj
If TypeOf .Object Is msforms.CheckBox Then
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End If
End With
Next OLEObj
End With
End Sub

And the same formula could be used.
=countif(yourrange,true)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thanks very much Dave...worked a treat.

You could use a macro to add the linked cell.

This code assumes that you used the checkbox from the Forms toolbar--not from
the Control Toolbox toolbar and it links the cell directly below the
checkbox--and hides the value in that cell using a custom format:

Option Explicit
Sub testme()
Dim CBX As CheckBox
With Worksheets("sheet1")
For Each CBX In .CheckBoxes
With CBX
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End With
Next CBX
End With
End Sub

Then you could use:
=countif(yourrange,true)
to count the number checked.

=======

This code assumes you used the checkbox from the Control toolbox toolbar--not
the Forms toolbar:

Option Explicit
Sub testme2()
Dim OLEObj As OLEObject
With Worksheets("sheet1")
For Each OLEObj In .OLEObjects
With OLEObj
If TypeOf .Object Is msforms.CheckBox Then
.LinkedCell = .TopLeftCell.Address(external:=True)
.TopLeftCell.NumberFormat = ";;;"
End If
End With
Next OLEObj
End With
End Sub

And the same formula could be used.
=countif(yourrange,true)

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm