Check Box Control

[pingodoce]

I want to display several individual groups of check boxes on a worksheet. For example there may be a group of four and a group of three.
I want the groups to be independant of each other.
I want there to be one check box selection only for each group i.e. if checkbox 1 is checked and then checkbox 2 is selected, checkbox 1 should automatically be deselected. This is how the option buttons work.
I can create want I want with option buttons but I need them to look like ticks or even 'X's. Is it possible to change the appearance of an option button to a 'tick' whilst keeping it's properties. Or would it be easier to create a group of check boxes and make them operate like option buttons? If so, how do I do it?

[mrice]

You can do this by linking each of your checkboxes to different cells and using the worksheet_change event to make them behave in the way that you want by changing the values of the cells by macro.

[pingodoce]

Thanks for your response.
I have been able to link the check boxes to the cells as advised but how do I implement the 'worksheet_change event'. Is this in Visual Basic? If so how would I code it?

[MSP77079]

I really liked MRice's idea. But, was unable to get it to work.

Here is the straightforward approach you asked about originally, I think ...

Please Login or Register  to view this content.

Not sure where to put this code? Right-click on the tab for the worksheet that contains the check boxes; select "View Code"; paste this code where you see the cursor flashing.

[pingodoce]

Thank you for your kind help, it works well for all check boxes on one worksheet.
I now have to find out how to apply this code to several individual groups on the same worksheet, so that they operate independently of each other.

I love this cooperation!

[mrice]

On trying this myself, I need to refine the idea as it appears that the
change event is not triggered by the clicking of a linked check box. I
had assumed that it would be.

You can get the desired behaviour with an extension to the idea by
linking a further cell (in the attachment in column C) by formula. Now a
click on the check box triggers the Worksheet_Calculate event.

There's a slight issue with not knowing which check box has been
selected so an additional flag (in the attachment in column D) is used
to denote which box was last selected and therefore needs to be
deselected.

The final challenge is to avoid nested calls to the subroutine with the
changes being made triggering their own Worksheet_Calculate events. This
can be got around by defining a module level boolean variable which is
set to TRUE when the first call is made (and set to FALSE when it
completes) and when found to be already set by the subsequent calls will
force them to terminate prematurely.

[MSP77079]

MRice, I still like your ideas best.

pingodoce,

I now have to find out how to apply this code to several individual groups on the same worksheet, so that they operate independently of each other.

Notice that the code I posted works based on the names of the checkboxes. In the example, I was using the default names ("CheckBox1", "CheckBox2", et cetera).

There are two ways (that I have used) to make groups of checkboxes act like option boxes, but in more than one "group".

1. give each group a different name. So, if the groups are, say, a group of countries and product lines, you could use "ckCountry1", "ckCountry2" and "ckProduct1", "ckProduct2". That way you can keep the groups separate by name.

2. use similar names, but assign blocks of numbers to different groups. Say, "Checkbox01" through "Checkbox99" is in group "A", and "Checkbox100" through "CheckBox999" is in group "B".

I guess another way would be to have one group of checkboxes be ActiveX controls and another group be Forms Controls. But, I have never tried that method.

[pingodoce]

Thanks a lot guys