Conditional Cell Link in Spinner?

[BJ]

I searched through the forum but I can't find the solution I'm looking
for. I want the Cell Link in a single Spinner control to depend on the
value of another cell. In other words, if A1=0, then the Spinner
control will index C1, and if A1=1 then the Spinner control will index
cell C2. Is this possible?

Thanks

 

[Bernie Deitrick]

BJ,

ry linking the spinner to a cell with the formula

=IF(A1=0,C1,C2)

HTH,
Bernie
MS Excel MVP

 

[BJ]

Bernie,

Thanks for the reply. I'm not sure I understand correctly. If I link
to the cell that contains the formula the formula is overwritten by
the spinner index value.

Thanks
BJ

 

[Bernie Deitrick]

BJ,

I'm sorry - I wasn't thinking clearly, and was obviously confused ;-)

Anyway, copy the code below, right click the sheet tab, select "View Code" and paste the code into
the window that appears. Just change the name SpinButton1 to the name of the spin button of
interest.

HTH,
Bernie
MS Excel MVP

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim mySB As OLEObject
Set mySB = Me.OLEObjects("SpinButton1")

If Range("A1").Value = 0 Then
mySB.LinkedCell = "C1"
Else
mySB.LinkedCell = "C2"
End If

End Sub

 

[Bernie Deitrick]

BJ,

One other thing.... If you are using a spinner from the Forms toolbar, then you would need to use
this instead:

Dim Shp As Shape
Set Shp = Me.Shapes("Spinner 1")

If Range("A1").Value = 0 Then
Shp.ControlFormat.LinkedCell = "C1"
Else
Shp.ControlFormat.LinkedCell = "C2"
End If

HTH,
Bernie
MS Excel MVP