spinner control in chart sheet... please help!

[Guest]

I have a worksheet with A LOT of data...
x Y Y1 Y2 Y3
data data data data data
.... .... ..... ..... .....

I have a chart sheet with all data plotted (xy scatter) and really need
something like a spinner button so that I can scroll and only plot a block of
my data at a time and have the chart update automatically with the contol.
Doesn't have to be a spinner button, but it was a guess on my part. I have
now bought 3 LARGE VBA for Excel books and am still unable to do seemingly
simple tasks I need. Could anyone take me step-by-step on how to set this up,
please?? Keep in mind I need it "dumbed down" to my basic beginner's level.
Thanks so much in advanced for any help you can give.
Anna

 

[Andy Pope]

Hi,

It's not a spinner but a scroller. The principle is the same though.
You need to use a dynamic named range for the charts data source.
http://www.andypope.info/charts/Scrolling.htm

Cheers
Andy

 

[Guest]

Thanks! However, I have over 38,000 rows of data and the scroll bar only
allows a max of 30,000. Is there a way to just tie the x axis min and max
scale to a scroll bar (in increments of 0.001... time is the x axis) so I can
scroll through my data that way?
Thanks again for the help!
Anna

 

[Andy Pope]

A data series will only allow 32k points so I'm assuming your chart has
multiple series.

You can not link the scales min or max values directly to cells so you
would need to use code in the scroll bars events.

Add a scrollbar with Max value of 200. Linkcell = A1
Then add this code.

Private Sub ScrollBar1_Change()

With ActiveSheet.ChartObjects(1).Chart.Axes(2, 1)
.MinimumScale = Range("A1").Value / 1000
.MaximumScale = .MinimumScale + 0.001
.MajorUnit = 0.0001
End With

End Sub
Private Sub ScrollBar1_Scroll()

With ActiveSheet.ChartObjects(1).Chart.Axes(2, 1)
.MinimumScale = Range("A1").Value / 1000
.MaximumScale = .MinimumScale + 0.001
.MajorUnit = 0.0001
End With

End Sub

Cheers
Andy