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 |
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/ |
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 |
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 |
All times are GMT +1. The time now is 11:40 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com