Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option button task
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option button task
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. --- Message posted from http://www.ExcelForum.com/ |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option button task
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 D. Miller wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Option button task
Thanks. Just what I was looking for.
"Debra Dalgleish" wrote in message ... 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 D. Miller wrote: 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 -- Debra Dalgleish Excel FAQ, Tips & Book List http://www.contextures.com/tiptech.html |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
where will i get task pane option in Excel | Excel Discussion (Misc queries) | |||
Option Button | Excel Worksheet Functions | |||
keep source formatting is not an option in paste option button | Excel Discussion (Misc queries) | |||
Option Button Help | Excel Worksheet Functions | |||
Can I create a button in Excel that generates a task in Outlook? | Excel Discussion (Misc queries) |