Select All Checkbox

[ChainsawDR]

hi all,

I'm using Excel 2007.

I have 20 check/tick boxes on my worksheet (CheckBox1-20), each of the 20 tickboxs has its own linked cell A1-A20 - I wish to have another checkbox (checkbox21) that, when ticked will tick all of them.

Also, if unticked it will untick the 20. I'd like this to be a kind of "onclick" command - so that if I wanted to tick 5 of the 20 on their own, the "master" tick box won't interfere with this. If a command button is easier to code than this is just as good.

I've been looking online on on here for the last 2 hours with no joy, not even getting close. Any help would be greatly appreciated.

I am using Form Controls rather than ActiveX controls.

Kind regards

ChainsawDR

[Andy Pope]

Something like this, where the routine CheckAll is assigned to the master checkbox.
You may need to change the control names.

Please Login or Register  to view this content.

[ChainsawDR]

it worked a treat, thank you so very much Andy.

[Jack in the UK]

or -- try

Please Login or Register  to view this content.

[Andy Pope]

Jack, you have to be careful when checking the value of the checkbox.
Value is not a true boolean. The checkbox can have 4 different values controlling it's 3 states.

0 or -4146 when unchecked
1 when checked
2 when mixed state


ps: Good to see/read you back in the world of xl

[Jack in the UK]

Cheers Andy, very rusty thou been a good year or more out

Ermm code worked in testing, cheers for the pointer.

[Andy Pope]

To illustrate the problem,
Uncheck all the checkboxes and with the check box 1 unchecked run your code.

The unchecked state returns -4146 which the code treats as TRUE and therefore sets all the other checkboxes to checked.

Edit: just uncovered the constants

Please Login or Register  to view this content.