Option button task

  • Thread starter Thread starter D. Miller
  • Start date Start date
D

D. Miller

I'm not sure if this is possible or not, but I would like to use two option
buttons to determine the contents of a cell, which is dependent on the value
of another cell. As an example, if I insert 100 in cell A1, cell A2 would
display "Blue" if option button 1 is selected. However, if option button 2
is selected, then cell A2 would display "Red", (100 would still be in cell
A1). If I insert 200 in cell A1, then cell A2 would display "Purple" with
option button 1 selected or "Green" with option button 2 selected. Cell A2
would be able to return two different values of whatever was in cell A1,
depending on which option button was clicked. Any help with this would be
greatly appreciated. Thanks in advance.

D. Miller
 
1. On the worksheet, using the Forms toolbox set up 2 option buttons in
a frame and set the same Cell Link (here $D$2 but can use any) - which
automatically changes to 1 or 2 depending on the button clicked. Right
Click and assign the same macro to each button so it will run when it
is clicked. (alternatively could use the Change event or button to
run).


2. Conditional formatting is no use here, so we need a macro. Copy and
paste this :-

'------------------------------------------------
Sub test()
Dim FillColour As Integer
Blue = 8
Purple = 38
Red = 46
Green = 4
MyValue = ActiveSheet.Range("A1").Value
MyButton = ActiveSheet.Range("D1").Value
'---------------
If MyValue = 100 And MyButton = 1 Then
FillColour = Blue
ElseIf MyValue = 100 And MyButton = 2 Then
FillColour = Red
ElseIf MyValue = 200 And MyButton = 1 Then
FillColour = Purple
ElseIf MyValue = 200 And MyButton = 2 Then
FillColour = Green
Else
MsgBox ("Cannot resolve.")
Exit Sub
End If
ActiveSheet.Range("A2").Interior.ColorIndex _
= FillColour
End Sub
'--------------------------------------------------


3. For the *contents* of A2 you can either use a formula in the cell
using A1 and D1 or put the calculation in the macro.
 
If you used option buttons from the Forms toolbar, you can link them to
a cell --
Right-click on the option button
Choose Format Control
Select the Control tab
Click in the Cell Link box, and then click on a worksheet cell,
e.g. $C$1

Set up a table with the values and options, e.g.

100 Blue Red
200 Purple Green

In cell A2, enter a formula that looks up the applicable value:

=VLOOKUP(A1,I2:K3,C1+1,0)

where the lookup table is in cell I2:K3
 
Thanks. Just what I was looking for.


Debra Dalgleish said:
If you used option buttons from the Forms toolbar, you can link them to
a cell --
Right-click on the option button
Choose Format Control
Select the Control tab
Click in the Cell Link box, and then click on a worksheet cell,
e.g. $C$1

Set up a table with the values and options, e.g.

100 Blue Red
200 Purple Green

In cell A2, enter a formula that looks up the applicable value:

=VLOOKUP(A1,I2:K3,C1+1,0)

where the lookup table is in cell I2:K3
Click to expand...
 
Back
Top