VBA to resize multiple Chart Series via A Scrollbar.

[NatetheGreat]

Hello,

I currently have a beautiful bit of code assigned to a scrollbar, that was suggested to me here on Mr.Excel. Thanks to all the Gurus out their for their continual support and assistance.

Sub Scrollbar1_Change()
Select Case ActiveSheet.ScrollBars("Scroll bar 3").Value
Case "0"
Call FullChartDataRange
Case "1"
Call HalfChartDataRange
Case "2"
Call QuarterChartDataRange
Case "3"
Call eighthChartDataRange
Case "4"
Call SixteenthChartDataRange
End Select
End Sub

Each of these cases call a very similar sub, which are just resizing the range of a series on the active chart

Sub HalfChartDataRange()
Dim Rng As Range
    
    ActiveSheet.ChartObjects("Rolling Position").Activate
    Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
    ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 2)
    
End Sub
Sub QuarterChartDataRange()
Dim Rng As Range
ActiveSheet.ChartObjects("Rolling Position").Activate
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
    ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)
End Sub

there are 5 in all... each of these adjusting the range of the chart to different sections of the data series.

this works great, but does anyone know what the code would be to apply the same logic to a different chart which had not just one, but 3 series within it? All three series are linked to the same table which the same x-axis values, just looking to adapt

  Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
    ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 2)

So that it will re-size (split in half) three series instead of just the one.


Thanks so much !

 

[Andrew Poulsom]

What is the source for the chart?

 

[NatetheGreat]

What is the source for the chart?

Andrew,

The source for the chart would be on the same table "Raw Data", it gets created via the following code which may be helpful

 ActiveChart.SeriesCollection.Add _
        Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum Bid]]")
    
ActiveChart.SeriesCollection.Add _
        Source:=Worksheets("Raw Data").Range("Stats[[#All],[Cum Ask]]")

so in total their are three series, all in the stats table

1)Cum Ask, 2)Cum Bid, 3)Rolling Position

 

[Andrew Poulsom]

Can you post some sample data for me to work with please?

 

[NatetheGreat]

[TABLE="width: 500"]
<TBODY>[TR]
[TD]Rolling Position[/TD]
[TD]CumOffr[/TD]
[TD]CumBid[/TD]
[TD]CellRef[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]24[/TD]
[TD]10[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]26[/TD]
[TD]15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]27[/TD]
[TD]16[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]22[/TD]
[TD]17[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15[/TD]
[TD]19[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14[/TD]
[TD]20[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13[/TD]
[TD]12[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]11[/TD]
[TD]13[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]9[/TD]
[/TR]
</TBODY>[/TABLE]

So You can Imagine I have my chart that has these three series plotted on it. The cellref is the x-axis. Say the user wanted to show just the first 5 values (i.e. cell ref 1-5 and corresponding series values), that is the purpose of resizing via the scrollbar.