I put a spinner from the Forms control toolbox on the worksheet.

I called it "Spinner 1".

Then I used this code behind the worksheet:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim mySpinner As Shape
Dim myVal As Long
Dim LinkedCellColumn As String

LinkedCellColumn = "BX"

Set mySpinner = Nothing
On Error Resume Next
Set mySpinner = Me.Shapes("Spinner 1")
On Error GoTo 0

If mySpinner Is Nothing Then
MsgBox "Design error--no spinner"
Exit Sub
End If

If Target.Cells.Count 1 Then
mySpinner.Visible = False
Exit Sub
End If

If Intersect(Me.Range("17:31"), Target) Is Nothing Then
mySpinner.Visible = False
Exit Sub
End If

mySpinner.Visible = True
With Target
mySpinner.Top = .Top
mySpinner.Left = .Offset(0, 1).Left
myVal = Me.Cells(Target.Row, LinkedCellColumn).Value
mySpinner.OLEFormat.Object.LinkedCell _
= Me.Cells(Target.Row, LinkedCellColumn).Address(external:=True)
Me.Cells(Target.Row, LinkedCellColumn).Value = myVal
End With

End Sub

When I select a cell in rows 17:31, the spinner pops up to the cell to the right
in the same row--don't select IV or add some code to stop that error!





mastermind wrote:

I have a range containing vlookup formuals (AX17:AX31). The index
number for these functions are contained in a separate range
(BK17:BK31), and are each manipulated by a seperate spinner button. I
am curious if there is a way to make it so that when a particular cell
is selected only the button affecting the formula is visible. Is this
even possible? If anyone can think of a better way to do this let me
know. I don't know if there is a way to make a floating button that
aligns itself/adjusts the formula of the selected cell. Any ideas or
solutions will be greatly appreciated. Thank You.

--

Dave Peterson