Setting value of linked cell for a Scroll Bar

[mxweb]

Hello, I am trying to dynamically set the value of the linked cell for a Scroll Bar. I dont mind if the scroll bar is form type or active x type.

Basically I have a column with figures in each cell moving down:

15
19
23
56

I have a scroll bar

I want the scroll bar to change the value in the active cell.

So if I select the cell with value 15 then I want that to change when I change the scroll bar. If I then select 56, I want that value to change.

Been struggling with this so any help would be appreciated.

 

[mxweb]

OK I just figured it out - for anyone interested (it took me all nite to sort this one!) here is what I did:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
ScrollBar1.LinkedCell = ActiveCell.Address
End Sub

Pretty simple really.

But, does anyone know how to make it so it only sets the linked cell when the active cell address is within a defined range (ie, only when they click on my data range)

 

[VoG]

Try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A5")) Is Nothing Then ScrollBar1.LinkedCell = Target.Address
End Sub

Note that I changed ActiveCell to Target.

 

[mxweb]

Hi, thanks for that, but when I clicked outside the range it still changed the last active cell within range if I used the scroll bar. Maybe I did something wrong?

What I settled on was (and it is probably noob messy, but main thing is it works) disabling the scroll bar when user clicked outside range, and enabled it when user clicked inside range, and setting the linked cell:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim rngTest As Range
Set rngTest = Application.Intersect(ActiveCell, Range("B6:B10"))
If rngTest Is Nothing Then
ScrollBar1.Enabled = False
Else

ScrollBar1.LinkedCell = ActiveCell.Address
ScrollBar1.Enabled = True
End If

End Sub