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.

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

Hey, thanks a lot. The code works great. However, I need a little bit
of help in modifying it now. The cells containing the Vlookup formulas
are merged cells (AX17:BG17....all the way down to the last row
AX31:BG31), and while the code works great on a single cell it will not
run when the cells are merged. Is there a way to make it respond to
the merged cells? Thank you


Dave Peterson wrote:
> 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!

You mean it fails when it tries to put the spinner in the cell to the right?

If that's the case, maybe you can put it in the cell to the left
(.offset(0,-1)).

If you mean that it's a problem when you change selection, then maybe just
dropping that check for multiple cells:

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

Set Target = Target.Cells(1)

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


(I hate merged cells!)

mastermind wrote:
>
> Hey, thanks a lot. The code works great. However, I need a little bit
> of help in modifying it now. The cells containing the Vlookup formulas
> are merged cells (AX17:BG17....all the way down to the last row
> AX31:BG31), and while the code works great on a single cell it will not
> run when the cells are merged. Is there a way to make it respond to
> the merged cells? Thank you
>
> Dave Peterson wrote:
> > 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!

--

Dave Peterson