ScrollBar - set MIN/MAX cell value

[SteveT]

Hello,
I have a template sheet that uses cell "M1" value as the row match within
index functions throughout the sheet.

I'd like to use a scrollbar to allow users to change the "M1" value using
the scroll bar in turn changing results of the index function while staying
within perimeters of cell "M2" (The Minimum) AND cell "M3" (The Maximum).

These variables (MIN & MAX levels) will change each time template document
is entered by user, requiring the MIN & MAX settings of scrollbar to reset to
the "M2" and "M3" values

I believe this can be accomplished from the limited amount of info browsed
through on web using event macro but I've had no success sorting through
myself.


Any Help would be greatly appreciated. Thanks,

 

[Patrick Molloy]

paste this into the sheet's cod page (right click sheet tab, click View
Code)
In my case, the scroll bar has the default name. you may want to change this

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Select Case Target.Address
Case "$M$2", "$M$3"
setMinMax
End Select
Target.Select
End Sub

Sub setMinMax()
Dim ctrl As ScrollBar
Shapes("Scroll Bar 1").Select
With Selection
.Min = Range("M2")
.Max = Range("M3")
End With
End Sub

 

[SteveT]

Thanks Patrick for your response but I'm getting
Run-time '438' error
'Object doesn't support this property or method'

I managed by tinkering to get following to work, but it fails to work
automatically when i exit the sheet after the last settings = Min 1 / Max 1.
In other words as long as my Max stays above 1, i can rely on below to change
automatically upon entry into sheet.

Private Sub ScrollBar1_Change()
With ScrollBar1
.Min = Range("M2").Value
.Max = Range("M3").Value
End With
End Sub

I can reset to begin working again if i manually run the event in Visual
Basic but I'd like it if my entry macro (mac that takes user to this screen)
auto triggers the scrollbar reset.

Thanks again / Brgds