Scroll Bar maximum value

[Guest]

I'm designing an interactive chart using a scroll bar object from the Forms
Toolbar. The data for my chart gets updated daily and I'd like to have the
Maximum Value of the scroll bar reflect the maximum number of data points.
Is there a way to do this? The Maximum Value entry does not seem to accept a
cell value or formula. I appreciate any help you can offer to solve this
problem.
Regards,

 

[Andy Pope]

Hi,

The Maximum property can not be linked to a cell but you could use a
cell to store the value and update the scroller if that value changes.

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
If IsNumeric(Target.Value) Then
ActiveSheet.Shapes("Scroll bar 1").ControlFormat.Max _
= Target.Value
End If
End If
End Sub

Change cell location and control name to suit.

Cheers
Andy

 

[Guest]

Lee,

Adding to Andy's comments . . .

Instead of using the scroll bar in the Forms toolbar, replace it with a
scroll bar from the Controls toolbar. The Controls toolbar contains controls
that allow you a lot more flexibility.

Assume that your interactive chart is currently based on the data in cells
A1:A5. Add the numbers 3,6,4,5, and 2 into the range A1:A5.

Go to View -> Toolbox -> Controls Toolbar. Drag the Scroll bar control on
to your worksheet. Activate the design mode on the controls toolbar. Select
the scroll bar control by clicking on it once. Right-click on your mouse
and, while the Scroll bar control is still active, select View Code. Enter
the following code into the code module.

Private Sub ScrollBar1_Change()
Dim Rng As Range
cnt = 0
For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cnt = cnt + 1
Next Rng
With ScrollBar1
.Max = cnt
.Min = 1
End With
‘Range("C1").Value = ScrollBar1.Value
End Sub

If you want to see the results of the Scroll bar count, edit this line in:

Range("C1").Value = ScrollBar1.Value

As you add numbers to the end of the range, i.e. into cells A6, A7, A8,
etc., the maximum scrollbar value will be updated as the control is moved.

 

[Guest]

Andy and John,
Thanks a million. That's exactly what I needed. Both ways work in my
application. I really appreciate the help.
Regards,
Lee

 

[Jon Peltier]

You don't need this:

Range("C1").Value = ScrollBar1.Value

Controls Toolbox controls have a LinkedCell property which accepts the address of a
cell. Use the Properties button on the Controls Toolbox to view the Properties window.

If I understand your formula, this:

For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
cnt = cnt + 1
Next Rng

can be done without the loop:

cnt = Range("A" & Rows.Count).End(xlUp).Row

I like saving code whenever I can.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______