spinner control in chart sheet... please help!

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

anna - 09 Aug 2006 20:57 GMT

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

Reply to this Message

Andy Pope - 10 Aug 2006 08:40 GMT

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

> I have a worksheet with A LOT of data...
> x Y Y1 Y2 Y3
[quoted text clipped - 10 lines]
> Thanks so much in advanced for any help you can give.
> Anna

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message

anna - 10 Aug 2006 17:41 GMT

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

> Hi,
>
[quoted text clipped - 19 lines]
> > Thanks so much in advanced for any help you can give.
> > Anna

Reply to this Message

Andy Pope - 10 Aug 2006 20:16 GMT

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

> 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
[quoted text clipped - 26 lines]
>>>Thanks so much in advanced for any help you can give.
>>>Anna

Signature

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info

Reply to this Message