Allowing only 1 check box to be ticked

[Guest]

I have 10 check boxes linked to formulas e.g., if link cell is true do this
etc.
The problem is only 1 box should be ticked. If I tick another box it should
become the active choice and deactivate the previous tick.
Obviously check boxes can all be ticked as they are individual objects.
Is there a way to make check boxes behave like option buttons (I cannot
substitute option buttons as they do not provide true/false returns).
BTW I realise (and know) there is probably a better way to achieve the
result Im after, but there is a certain senior manager here who insists it
can be done using check boxes! :-(
cheers,
Nadia

 

[Anne Troy]

Place them in a group box.
*******************
~Anne Troy

www.OfficeArticles.com

 

[Guest]

Ive tried that before. I can still tick all check boxes even when in a group
box.

 

[Biff]

Just wondering why you can't use option buttons?

They do not return a boolean as you noted, but the one that's selected
returns a value (number).

Biff

 

[Guest]

Exactly my thought Biff, as I mentioned, I have a senior manager who "knows
better".
Will suggest he does it himself!
cheers,
Nadia

 

[Robert_Steel]

Is there a way to make check boxes behave like option buttons (I cannot

substitute option buttons as they do not provide true/false returns).

There are two types of option buttons. controls and forms

The ones that are created using the Controls toolbar do give a direct true
false return to a linked cell. The properties allow you to set groups up.

The ones created using the Forms toolbar return a number as you have
identified.
you could then extract the true false using =$A$1=1 =$A$1=2... in your
linked formulae

Obviously if the need for a tick box is purely asthetic then I concur with
you view of your boss.

hth RES

 

[Dave Peterson]

But you could use option buttons and just adjust the formula:

=if(linkedcell=1,
=if(linkedcell=2,

Instead of true/falses.

But you could have code that turns off the other checkboxes when you check any
of them...

I used checkboxes from the Forms toolbar so that I could assign the same macro
to each.

I put 10 checkboxes from the Forms toolbar on a worksheet.

Then I assigned this macro to each of them:

Option Explicit
Sub testme()

Dim ThisCBX As CheckBox
Dim CBX As CheckBox

Set ThisCBX = ActiveSheet.CheckBoxes(Application.Caller)

If ThisCBX.Value = xlOn Then
For Each CBX In ActiveSheet.CheckBoxes
If CBX.Name = ThisCBX.Name Then
'do nothing
Else
CBX.Value = xlOff
End If
Next CBX
End If

End Sub


If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

 

[Guest]

Thank you Dave and Robert,
I guess it really is for aesthetic reasons... but as they say... anything to
keep the boss happy!
many thanks
cheers,
Nadia

 

[Andrew]

Hi Dave - how easy is it to change your code so only the active row is
evaluated rather than the activesheet. In other words I have 4 check boxes
(from the forms toolbar - not using option buttons for aesthetic reasons) on
each row (about 40 rows) and only want the user to be able to check only one
box on each row. I tried to edit your macro but didn't have any luck. By the
way, I added the the worksheet using another macro you posted - thanks!

Andrew

 

[Dave Peterson]

First, I think you're going to be confusing your users by using checkboxes
instead of optionbuttons. Most users understand the difference between
checkboxes and optionbuttons.

But you can't just use:

dim CBX as checkbox
for each cbx in activecell.entirerow.checkboxes

This won't work.

But you can cycle through all the checkboxes and look at the position of each
checkbox.

For each cbx in activesheet.checkboxes
if cbx.topleftcell.row = activecell.row then
'do what you want...
end if
next cbx

But I wouldn't do this if I were you. I'd use the optionbuttons.

 

[Andrew]

Thanks for the info and advice. I agree with you on the option buttons vs.
check boxes, but I really don't think I get a choice in the matter.

Andrew

 

[BunnyHop]

Hi Dave,
I'm using Excel 2007, and new to using macros, and wondered how to do your
suggestion for multiple options on the same row. Only want one to be allowed
and then calculated using the options button (and are you using the option
button on forms or under active x control?)

Thanks for any help you can give.

EJ

P.S. I realize this is an old post but maybe you or someone else will still
be able to help.

 

[Dave Peterson]

If you only one a single selection in a group of choices, then the control you
should use is the optionbutton. That's one of its built-in features and the
typical user will have seen these and know how they work.

If you haven't designed your "form", you could use the macro at Debra
Dalgleish's site. It creates multiple optionbuttons from the Forms toolbar (not
the control toolbox toolbar) and puts each group inside a groupbox.

http://contextures.com/xlForm01.html

If you really, really wanted to use optionbuttons from the control toolbox
toolbar, it's possible--but I wouldn't recommend it. For lots of controls on a
worksheet, I've always found the controls from the Forms toolbar much better
behaved.

Hi Dave,
I'm using Excel 2007, and new to using macros, and wondered how to do your
suggestion for multiple options on the same row. Only want one to be allowed
and then calculated using the options button (and are you using the option
button on forms or under active x control?)

Thanks for any help you can give.

EJ

P.S. I realize this is an old post but maybe you or someone else will still
be able to help.