Count Check Boxes

[Guest]

I have created a column that has check Boxes using the Control Toolbar. How
do I count the total number of checkc boxs in a column, that have check marks
in them. I do not know VBA, only know how to create and paste a function was
already set up for me. Thanks in advance, Tony

 

[Dave Peterson]

You can set up a LinkedCell for each checkbox. (It's under properties when you
rightclick on the checkbox (while in design mode).)

Then you can put those in a column (hidden) in the same row as the checkbox.

then
=countif(e:e,true)
(use the correct column, though)

 

[Guest]

Thanks Dave,

This worked exactly like you said it would. I have 5,000 rows, do I have to
do the LINKCELL for every row.

 

[Dave Peterson]

How about something like this that will assign the linked cell to Column A of
the same row that holds the checkbox (from the Control toolbox toolbar):

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet
Set wks = Worksheets("sheet1")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.LinkedCell _
= wks.Cells(OLEObj.TopLeftCell.Row, "A") _
.Address(external:=True)
End If
Next OLEObj

End Sub

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

Change that "A" to whatever column you really want.

 

[Guest]

WOW! Marvelous, Fantastic, PERFECT,,,,,,,,Thanks

How about something like this that will assign the linked cell to Column A of
the same row that holds the checkbox (from the Control toolbox toolbar):

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim wks As Worksheet
Set wks = Worksheets("sheet1")

For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.LinkedCell _
= wks.Cells(OLEObj.TopLeftCell.Row, "A") _
.Address(external:=True)
End If
Next OLEObj

End Sub

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

Change that "A" to whatever column you really want.