Linking Scrollbar Min and Max values to worksheet cells



I'm not too familiar with controls, so I hope that what I'm trying to do is

I've placed a scrollbar control on my worksheet. There are MIN and MAX
values that can be manually entered in the properties page, but I need to
reference a worksheet cell for each of these parameters. Is there an easy
way to do this?




Bill Renaud

I am not a Controls expert, but try the following code:
I used cell $A$1 for the minimum value of the scrollbar, and cell $A$2 for
the maximum value. I left the scrollbar named as "ScrollBar1". You might
need to add some code inside of each If statement to check the values for
proper range before assigning them to the ScrollBar.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngScrollMin As Range 'Cell that contains Scrollbar minimum.
Dim rngScrollMax As Range 'Cell that contains Scrollbar maximum.

Set rngScrollMin = Range("A1")
Set rngScrollMax = Range("A2")

If Target.Cells.Count > 1 Then Exit Sub

If Not Intersect(Target, rngScrollMin) Is Nothing _
'Changed cell is the cell containing the ScrollBar minimum.
ScrollBar1.Min = CLng(rngScrollMin.Value)
Exit Sub
End If

If Not Intersect(Target, rngScrollMax) Is Nothing _
'Changed cell is the cell containing the ScrollBar maximum.
ScrollBar1.Max = CLng(rngScrollMax.Value)
Exit Sub
End If
End Sub

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question