How do I activate an option button?

[Option Button]

Let assume I have created 4 option buttons (from the control toolbox) and I
have named them as option1, option 2, option 3, and option 4
Let also assume that option 1 occupies cells D8 to F9, option 2 occupies
cells D12 to F13, option 3 in cells J8 to J9, and option 4 in cells J12 to J13
In cell B8, I want it to be equal to 1 if option 1 is selected, equal to 2
if option 2 is selected, 3 if option 3 is selected and 5 if option 4 is
selected
1. How do I activate an option button so that I can select either option 1,
2, 3 or 4? (If one option is selected, then the rest should be blank)
2. When one of the option is selected, how do assign my cell B8 with the
correct number?

 

[Luke M]

If not working as desired now by default, you need to create a group box and
have all your options buttons inside the box. This will force them to only
have 1 of the 4 selected at any one time. Then, go to format control, the
control tab, and in the cell link box, type
=$B$8

 

[Gord Dibben]

Private Sub OptionButton1_Click()
Range("B8").Value = 1
End Sub

Revise code for each of buttons 2 through 4


Gord Dibben MS Excel MVP


On Mon, 16 Mar 2009 12:08:30 -0700, Option Button <Option

 

[Formatting]

What do I need to do after I revise the code for each button? Revising the
code as suggested by itself does not do anything.

 

[Luke M]

Sorry, missed the line in your original post.

For both mine and Gord Dibbon's solutions to work, you need to use the
option buttons from the "Forms" toolbar, NOT controls. Objects from the forms
toolbar have the cell link function (my solution) and the assign macro
ability (Gord's solution)

 

[Rick Rothstein]

So it is clear that what Gord suggested is what you did, you should have the
following code (I used your control names rather than the default control
name that Gord used in his example; and note that, contrary to your posting,
your control names cannot have a space character in them)...

Private Sub Option1_Click()
Range("B8").Value = 1
End Sub

Private Sub Option2_Click()
Range("B8").Value = 2
End Sub

Private Sub Option3_Click()
Range("B8").Value = 3
End Sub

Private Sub Option4_Click()
Range("B8").Value = 4
End Sub

And this code needs to be located in the code window for the worksheet where
your OptionButtons are located.

 

[Gord Dibben]

Luke

I used an option button from the Control Toolbox and did not "assign macro".

Right-click on it and "View Code"

You will see in the sheet module.

Private Sub OptionButton1_Click()

End Sub

Insert the Range("B8").value = 1 between the two lines.

Add another Option button to the sheet and same thing except

Range("B8").value = 2


Gord

 

[Rick Rothstein]

Gord's solution is not using "Assign Macro"... it is using event code... and
his code (along with his instructions) works fine for me (see my follow-up
message in Gord's sub-thread).

 

[Gord Dibben]

Thanks Rick.

I did not notice the name changes for the option buttons.


Gord

 

[Formatting]

Hi Luke,

Thank you for your help. When using the forms toolbar, I can't open view
code nor revise code. Also, with Forms toolbar,when I add another button and
change the cell assignment (ie $B$8 for button one, and then added button 2
and set it =$B$9), the original button =$B$8 changes to =$B$9 too. How do I
keep the button 1 as it was and modify only the button 2?

Thanks

 

[Dave Peterson]

How about an alternative...

Remove the optionbuttons from the control toolbox toolbar and replace them with
optionbuttons from the Forms toolbar.

You'll have to put a groupbox around these 4 if you have more optionbuttons on
the worksheet.

Then Rightclick on any of the 4 optionbuttons
Choose Format|Control
On the control tab, set the linked cell to your cell.

If you really meant that optionbutton1 is 1, optionbutton2 is 2, optionbutton3
is 3 and optionbutton4 is 5 (not 4), you can use an out of the way linked cell
and put a formula in the cell you want to see.

=if(z99=4,5,z99)

(where z99 is that out of the way cell)

 

[Formatting]

I am missing something. Let me start from the beginning.

1. Open a new book
2. Insert Option Button from Control Toolbox. I name it OptionButton1 and
place it in cells D19-E20
3. Insert a second Option Button from Control Toolbox. OptionButton2 in
cells D23-E24
4. Repeat step 3 for a third OptionButton3 in cells D27-E28
5. Go to OptionButton1, Right Click and under View Code, insert code:
"Range("B8").Value = 1"
6. Go to OptionButton2, Right Click and under View Code, insert code:
"Range("B8").Value = 2"
7. Go to OptionButton3, Right Click and under View Code, insert code:
"Range("B8").Value = 3"

What do I do next to be able to select either OptionButton1, 2 or 3?

 

[Formatting]

Thank you everyone

How about an alternative...

Remove the optionbuttons from the control toolbox toolbar and replace them with
optionbuttons from the Forms toolbar.

You'll have to put a groupbox around these 4 if you have more optionbuttons on
the worksheet.

Then Rightclick on any of the 4 optionbuttons
Choose Format|Control
On the control tab, set the linked cell to your cell.

If you really meant that optionbutton1 is 1, optionbutton2 is 2, optionbutton3
is 3 and optionbutton4 is 5 (not 4), you can use an out of the way linked cell
and put a formula in the cell you want to see.

=if(z99=4,5,z99)

(where z99 is that out of the way cell)

 

[Gord Dibben]

You don't really insert the option buttons into cells, just lay them on top.

Just click on the option button after you have disabled "design mode" on the
Control Toolbox.

Only one button can be active at a time.


Gord Dibben MS Excel MVP