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?

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Option Button" wrote:

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?

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

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?

When I right click the mouse when it is placed above the button, I get the
Format Control window. However, the control tab does not exist. Can you show
the step by step on how to link the option button?

Thanks

"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Option Button" wrote:

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?

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.

"Gord Dibben" wrote:

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

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?

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Formatting" wrote:

When I right click the mouse when it is placed above the button, I get the
Format Control window. However, the control tab does not exist. Can you show
the step by step on how to link the option button?

Thanks

"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Option Button" wrote:

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?

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.

--
Rick (MVP - Excel)


"Formatting" wrote in message
...
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.

"Gord Dibben" wrote:

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

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

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

On Mon, 16 Mar 2009 12:59:09 -0700, Luke M
wrote:

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)

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).

--
Rick (MVP - Excel)


"Luke M" wrote in message
...
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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Formatting" wrote:

When I right click the mouse when it is placed above the button, I get
the
Format Control window. However, the control tab does not exist. Can you
show
the step by step on how to link the option button?

Thanks

"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Option Button" wrote:

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?

Thanks Rick.

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


Gord


On Mon, 16 Mar 2009 16:10:28 -0400, "Rick Rothstein"
wrote:

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.

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


"Luke M" wrote:

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)
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Formatting" wrote:

When I right click the mouse when it is placed above the button, I get the
Format Control window. However, the control tab does not exist. Can you show
the step by step on how to link the option button?

Thanks

"Luke M" wrote:

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
--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Option Button" wrote:

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?

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)

Option Button wrote:

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?

--

Dave Peterson

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?


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Formatting" wrote in message
...
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.

"Gord Dibben" wrote:

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

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?

Thank you everyone


"Dave Peterson" wrote:

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)

Option Button wrote:

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?

--

Dave Peterson

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

On Mon, 16 Mar 2009 14:06:01 -0700, Formatting
wrote:

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?


"Rick Rothstein" wrote:

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.

--
Rick (MVP - Excel)


"Formatting" wrote in message
...
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.

"Gord Dibben" wrote:

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

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?