counting checkboxes

  • Thread starter Thread starter icestationzbra
  • Start date Start date
I

icestationzbra

hello,

i have an excel sheet on which there are several checkboxes (form) in
different areas, divided into sections, of the sheet.

i would like to count the checkboxes that are checked/unchecked without
using a macro. that is, using some kind of an inbuilt xl function, such
as sum with an if condition.

is it possible?

thanks,

mac.
 
Checkboxes have a Linked Cell property so you could easly have these i
a column with a COUNTIF() function to add up TRUE or FALSE
 
Mac,

You need VBA for this I am afraid

Dim chk As Excel.CheckBox
Dim cnt As Long

For Each chk In ActiveSheet.CheckBoxes
If chk.Value = 1 Then
cnt = cnt + 1
End If
Next chk

MsgBox cnt & " of " & ActiveSheet.CheckBoxes.Count & " checkboxes
checked"


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Since the checkboxes are from the form toolbar, right-click the checkbox
and choose Format control.

In the Control pane, select a cell (in an out-of-the-way location, say
Z1) in the Cell link refedit box. Do the same for the next checkbox,
using, say cell Z2.

Your count of checked boxes is then

=COUNTIF(Z:Z,TRUE)

Unchecked boxes:

=COUNTIF(Z:Z,FALSE)

If you wish, you can link the cells to a separate sheet and then hide
the sheet.
 
hello,

thanks for the countif tip. it worked since i am using form
checkboxes.

i am facing a peculiar problem. please tell me if it is a standar
feature so that i dont bang my head against it for long.

suppose, i have checkboxes from A15 to A30. i want the count to appea
cell A31. when i select the countif range as A15:A30 and the criteri
as TRUE, in the cell A31 i do not get any value. the cell is blank
however, when i put in the same formula in the next column, say B31,
do get the value.

i have checkboxes intermittently along a column. i would like to ge
subtotals for each section, and then an grand total at the end of th
column.

can i not get the value in the same column as a subtotal?

please advise.

mac
 
hello again,

i am afraid, it was my mistake. i had chosen the cell format as Custo
and ';;;' due to which nothing was showing up.

it works fine.

thanks for all the help.

regards,

mac
 
Back
Top