Using Countif with object properties.

A checkbox in my worksheet has the following properties:

Please Login or Register  to view this content.

.Value = xlon as I understand means the check-box is checked

Is it possible to use Application.WorksheetFunction.Countif to count, in a range of tick-box objects, whether they have the 'xlon' value?
Using:

Please Login or Register  to view this content.

I realise that I might be making some naive mistakes with this code - but basically I want to count using the inherent properties of the check-boxes, rather than count from the linked cells.

I hope this makes sense.

LinkedCell property only appears for activeX checkboxes, hence not in the CheckBoxes collection.

try:

Please Login or Register  to view this content.

Originally Posted by Andy Pope

not true. This worked for me when a Forms control checkbox was placed on the worksheet

Yes it works if Forms check boxes were used, no doubt about that, but he is using ActiveX check boxes.

Thanks both - I can confirm that I'm using Form Controls - and the code supplied works perfectly. I avoid activeX because I have yet to get my head round them to the same extent - also to link cells to them I have to right click and choose properties, as opposed to simply specifying the linked cell in the toolbar - small thing, but the time spent really adds up!

There's a couple amongst my 361 check-boxes(!) which seem immune to the count - as in, they don't move up or down when ticked - is there anything obvious I'm missing which could cause the issue?

Also - if you don't mind - can I alter the code to specify a certain range - so that I count 1 To 10, for example?

Andy - I've attached an example workbook which I've knocked together to illustrate the situation your code is helping me to aaddress:

I have made 6 identical sheets - these are sheets for each user to fill in. I've pasted in an example question.

The reason I asked for your code is so that I can quickly tally certain boxes. For example, I want to know how many people ticked 'None' for the sentencing occasion on the sheet. So that would be counting a specific range of boxes, 1 on each sheet.
As you can see it's easy to make some tallies manually when there's as few sheets and questions as in the worksheet I posted - but it becomes much harder when you have 360 boxes on each sheet and 100 sheets!

Andy that's incredible - it really does work like a dream

In theory, if I have five sheets with 1 check-box in each, all called 'Check box 1' (excel seems to restart the count for each different worksheet), could this macro count For each Worksheet, whether check-box 1 is ticked, and then produce a total? I.e only box 1.

You make a good point about Option buttons - however, I chose a bad example because most of my questions are not mutually exclusive - e.g(fictional), "what are your symptoms?"

Ah - I didn't see where you'd written 'None' in the code at first.

I am positively gleeful right now - I can't tell you how much this helps me/ how much you've taught me!

Thanks so much