Copying Check Boxes Formatting

Hi Jeff

Thanks, the first point you mentioned is my problem. Is your VBA code generic enough to be able to use on my sheet?

Also, is there a way of counting the number of ticks per column or row?

The second question is easier. If you have the checkboxes linked to cells, then counting the ticks in a row is

=COUNTIF(5:5,TRUE)

will count the number of checked boxes in row 5. Similarly for column C

=COUNTIF(C:C,TRUE)

This is not really counting the checkboxes, it's counting the cells they're linked to.

For the VBA code I have to look a couple of things up. I never use Forms controls except to answer this type of question (I use ActiveX controls) so I'm not real sharp on using VBA to manipulate them. But it's doable, let me get back to you on that one.

In the file you attached you have 41 checkboxes. I think you have probably been copying and pasting on top of prior work. There are only 18 visible. I have cleaned that up and provided a macro to initialize the linked cells. This is a demo; you probably don't really need a button to do it.

Demo file [CF for checkbox linked cells=CheckBox.xlsm]

It sets the linked cell as the cell containing the top left corner of the checkbox. Be aware that this does not mean the top left corner of the actual box that gets checked--the checkbox control has a bigger outline that you can see when you right-click it.

Image to illustrate the corner [checkboxcorner.jpg]

Thanks. That is brilliant.

I am struggling to use the code in my new workbook though? do i need to change any of it to adapt (sheet names etc)?

And How do you copy the checkboxes without them duplicating (to avoid the problem of having multiple boxes under each other)? cos when i select the cells and copy paste, it adds boxes automatically?

The code is in the same module for the sheet containing the checkboxes. Do you have more than one worksheet that contains checkboxes? There are two ways to handle this.
One is to copy my code from the Sheet2 code module into the sheet module for every worksheet you have, and execute them each separately (you can click anywhere in the code and hit F5 to run it). No changes are necessary. That is probably good enough if you only have to do this once.

Another option is to modify the code and put it in a new Module. Then it could loop through all sheets and execute the modified code at all once for all worksheets.

I am assuming you have some knowledge of macro code. If not, then I will need to give you a little more explanation of how to do the above.

I am not clear on your last question. Here's what I did to create this sheet: In cell C3, I created a checkbox so that top left corner was contained within C3. Then I copied the cell, and pasted it into the other 17 cells. I can't figure out how you created duplicate checkboxes in the same cells, except it's clear you must have pasted to those cells more than once.

Thanks, i have managed to get it sorted out. Really appreciate it.

6StringJazzer
Thank you so much for this. Unfortunately, it seems my coding isn't as good as I thought. I named the Range in Question CheckBoxes, but I am still getting Runtime error 424 Object Required. When I select Debug, it highlights this line:
>>For Each cb In CheckBoxes<<
What am I missing?
Many thanks in advance.