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" wrote:

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 wrote:

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" wrote:

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

Nadia wrote:

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

--

Dave Peterson


--

Dave Peterson