Control Cell Link for Option Button based on value in a cell

  • Thread starter Thread starter arunjoshi
  • Start date Start date
A

arunjoshi

I have two option buttons on a sheet. In cell A1, on the same sheet
there is a value which can be either 1 or 2.

Can you help me achieve this? ...

If the value in cell A1 is 1, then the Control Cell Link for the tw
option buttons should become cell B1.

If the value in cell A1 is 2, then the Control Cell Link for the tw
option buttons should become cell B2
 
I think you're going to need a macro. And that means you'll have to be careful
about which optionbutton is in use. (There's an optionbutton on the forms
toolbar and one on the control toolbox toolbar.)

I guessed that you used the control toolbox toolbar option buttons--and I also
guessed that A1 gets changed by typing (not the result of a formula).

If all that's true, right click on the worksheet tab with the optionbuttons and
select view code. Paste this in the code window (usually to the right):

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a1")) Is Nothing Then Exit Sub

If Target.Value = 1 Then
Me.OptionButton1.LinkedCell = Me.Range("b1").Address(external:=True)
Else
Me.OptionButton1.LinkedCell = Me.Range("b2").Address(external:=True)
End If

End Sub

If the value changes to 1, then you have B1 as the linkedcell. Anything else
(2, 3, empty, anything) will make B2 the linked cell.
 
Back
Top