Make Check Boxes behave as Option buttons

Hello nikolab,

Welcome to the Forum!

I have to ask why you don't want to use option buttons. Option buttons are designed to be mutually exclusive - only one sets at a time. What type of form are you referring to: a worksheet or VBA user form?

If your checkboxes are not Active-X type, then I don't see how you can get this to work cleanly without a lot of messy code. I would delete them and use Option buttons.

If your check boxes are from the Active-X type controls then you could use the click event with code similar to this:

Please Login or Register  to view this content.

Here is a formula approach that might work for you, but it will take A LOT of manual work to get it set up.

Assume three check boxs linked to cells A1:A3
Using cells B1:B3 to contain a formula to test each other, you might use this approach:

B1: =IF(AND(A1=TRUE,OR(A2=TRUE,A3=TRUE)),"Make one Selection ONLY",A1)
B2: =IF(AND(A2=TRUE,OR(A1=TRUE,A3=TRUE)),"Make one Selection ONLY",A2)
B3: =IF(AND(A3=TRUE,OR(A1=TRUE,A2=TRUE)),"Make one Selection ONLY",A3)

It will not stop them from checking more than one box in the absolute sense, but it does return a message and can return a corect value when all is in order.

I think you have made a classic mistake. Doing so many checkboxes should not be done statically in the first place. See what it brought you. Sorry I had to make that comment. Why not delete them all and use:

Please Login or Register  to view this content.

If you keep a reference table you can replace Checkbox1 with OptBut(1) etc in the code you have already.
This would be my approach.

Hello nikolab,

Since the behaviour of the CheckBox control can not be changed to do what you want, I agree with rwgrietveld. It is your best option this late in the game to VBA to correct your code.

The help you even a bit further:

Please Login or Register  to view this content.

Given a useform, this will replace all check boxes with option buttons, preserving their postition and captions. Note that the name of the new option button is the same as the checkbox so that the event code does not need to be rewritten.

This should be run once at design time, it need not be run every time at runtime.

Please Login or Register  to view this content.

Mike,

Nice code, looks good but for me this specific code

Please Login or Register  to view this content.

does not seem to work. VBProject failed.
Any idea?

In the VBEditor Tools>References, is your Microsoft Visual Basic for Applications Extensibility library checked?

I'm still on my first cup of coffee, but I don't think that the code should crash there if the VBIDE isn't active, it should crash somewhere else.

Also, are you on a PC? I'm on a Mac and this code is right in the middle of differences between the platforms. It would be good to know if it works on both.

One quick update, I just tested the code on the Excel 2007 at work and it ran OK.