Using Scroll Macros assigned to a Scroll Bar in VBA

[NatetheGreat]

Hi All,

I have a chart that has a series of values as the underlying selected data. I want to be able to see the full series, half the series, and a quarter of the series.

Thanks to the geniuses of this forum, I have been provided with the following codes to make that happen.

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

End Sub
Sub QuarterRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)
End Sub


Sub FullRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
End Sub

I want to have all of these attached to a scroll bar, where by clicking up or down I am scrolling from the short term (count/4) to the Mid Term (count/2) to the Full term (count/1). Is there a way to attach these three separate procedures to the scroll bar so that I can tick through it in such a fashion ?

many Thanks for all your continual help and support fellow mr.excel-ers

 

[Andrew Poulsom]

Can't you test the value of the ScrollBar (using If or Select Case) and call the appropriate procedure?

 

[NatetheGreat]

Mr.Poulsom,

How would I go about doing that ?

 

[Andrew Poulsom]

Use the ScrollBar's Change event procedure?

 

[Dweller]

Add a scroll bar control to your sheet. Format the scroll bar (right click, format control). Change the min/max to 0/2.

Assign a Macro to the scroll bar, by default it should be something like: 'ScrollBar1_Change'.

The macro code would contain the select case code:

Select Case ActiveSheet.Scrollbar1.value
Case "0"
...
Case "1"
...
Case "2"
...
End Select

Cheers,

Colin

 

[NatetheGreat]

Colin,

That's really interesting thank you. However if I have my three cases that I want, each one being the three following procedures

Sub HalfChartDataRange()
Dim Rng As Range
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
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)
End Sub
Sub FullChartDataRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
End Sub


Then how would I incorporate that with your suggestion? Would it be like this

Sub Butt*******()

Select Case ActiveSheet.Scrollbar1.value
Case"0"
Dim Rng As Range
SetRng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
Case"1"
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 2)
Case"3"
Sub QuarterChartDataRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)

End Select

End Sub


Then I would assign this macro to the scroll button?

 

[NatetheGreat]

The code above

Select Case ActiveSheet.Scrollbar1.value
Case"0"
Dim Rng As Range
SetRng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
Case"1"
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 2)
Case"3"
Sub QuarterChartDataRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)

End Select 

End Sub

Is failing with "Compile error:Duplicate declaration in current scope", when kicking it off either directly from the VBA project or via the scroll button that I have assigned the macro to. When it hits the 2nd Dim Rng As Range it fails.

 

[Dweller]

You can do what you did there, without the mistake (declaring a sub routine in the select case).

Or you can leave the code where it was, and call each sub routine:

[COLOR=#574123]Sub Scrollbar1_Change()[/COLOR]

[COLOR=#574123]Select Case ActiveSheet.Scrollbar1.value[/COLOR]
[COLOR=#574123]Case"0"[/COLOR]
[COLOR=#574123]call QuarterChartDataRange()[/COLOR]
[COLOR=#574123]Case"1"[/COLOR]
[COLOR=#574123]...[/COLOR]
[COLOR=#574123]Case"2"[/COLOR]
[COLOR=#574123]...[/COLOR]

[COLOR=#574123]End Select [/COLOR]

[COLOR=#574123]End Sub[/COLOR]

To assign a macro to the scrollbar, right click on it -> assign macro. Choose the appropriate name for the macro, and click 'New'.

 

[EPURON]

Colin,

That's really interesting thank you. However if I have my three cases that I want, each one being the three following procedures

Sub HalfChartDataRange()
Dim Rng As Range
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
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)
End Sub
Sub FullChartDataRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
End Sub


Then how would I incorporate that with your suggestion? Would it be like this

Sub Butt*******()

Select Case ActiveSheet.Scrollbar1.value
Case"0"
Dim Rng As Range
SetRng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
Case"1"
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 2)
Case"3"
Sub QuarterChartDataRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)

End Select

End Sub


Then I would assign this macro to the scroll button?

Nice sub name >_<

 

[NatetheGreat]

Hey Dweller,

When you say without the mistake of "declaring a sub routine in the select case" does that mean amending my code to

Sub ScrollButton()
Select Case ActiveSheet.Scrollbar1.Value
Case "0"
Dim Rng As Range
SetRng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
Case "1"
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 2)
Case "3"
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)
End Select
End Sub

Because now this is failing with
Compile error:
Duplicate declaration in current scope

Debugging on the 2nd Dim Rng As Range.

Im confused !

I tried doing it your way like the following

Sub Scrollbar1_Change()
Select Case ActiveSheet.Scrollbar1.Value
Case "0"
Call QuarterChartDataRange
Case "1"
Call HalfChartDataRange
Case "2"
Call FullChartDataRange
End Select
End Sub

Where on the same module below I have my original procedures

Sub HalfChartDataRange()
Dim Rng As Range
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
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 4)
End Sub
Sub FullChartDataRange()
Dim Rng As Range
Set Rng = Sheets("Raw Data").Range("Stats[RollPos]")
ActiveChart.SetSourceData Source:=Rng.Resize(Rng.Rows.Count / 1)
End Sub