VBA Scroll Bar Assignments

[NatetheGreat]

HI,

I have a scroll bar, which is formatted control current value 1 minimum value 0 and maximum value 2 with incremental change 1.

The following Code is all in module 3

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

this is supposed to be the case code that the scrollbar uses when clicking.

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

But whenever I click on the scrollbar, it just fails with a debug (object does not suppor this method)

Fails on Select Case ActiveSheet.Scrollbar1.Value


Please can someone help meeeeeeeee Im so stuck!

 

[Snakehips]

Nate,

Lets double check.

If you hover the mouse over the scrollbar does the cursor change to hand?

 

[NatetheGreat]

As per last sentence of my post #7. Right click scrollbar to select it and name is in the Name Box under the ribbon an lhs.

When I right click I have cut copy grouping order assign macro and format control. Format control. I am not seeing a name box or a ribbon named "an lhs"
.

 

[NatetheGreat]

Nate,

Lets double check.

If you hover the mouse over the scrollbar does the cursor change to hand?

yes it does change to hand

 

[Snakehips]

Good, so definitely not an Activex scrollbar.

Sorry, 'an lhs' = a typo . It should read on lhs - on left hand side!!!!

Also, If you are not on Excel 2007 up then you don't have ribbon, rather you have the menu bar.

Name Box is left of the formula bar in any version. Under the ribbon or under the menu bar.

When you right click scrollbar and have it selected, it's name will be in the Name Box.

 

[NatetheGreat]

Good, so definitely not an Activex scrollbar.

Sorry, 'an lhs' = a typo . It should read on lhs - on left hand side!!!!

Also, If you are not on Excel 2007 up then you don't have ribbon, rather you have the menu bar.

Name Box is left of the formula bar in any version. Under the ribbon or under the menu bar.

When you right click scrollbar and have it selected, it's name will be in the Name Box.

Thanks Tony. It was actually "Scroll Bar 3" and when changing this in the code it has gotten me past the case and controlformat.value errors/debugs. But now unfortunately I am getting errors on the actual called procedures, which has made me pull my hair out. When I click on the scroll bar it recognises case 1 as it should, calls HalfChartDataRange and fails on the last line with "object or with block variable not set".

This really doesn't make sense to me, because HalfChartDataRange works when I call the procedure directly just clicking play on it.

So confused and stressed!!

 

[Snakehips]

Confused and stressed of London….

When you Call, having clicked the scrollbar you do not actually have an ACTIVE chart!!! You will need to reference the chart by name or add a line to activate it.

Hope that sorts it.

 

[mikerickson]

If you select the scroll bar, its name should appear in the Name window (to the left of the formula editing field on my Excel.)

 

[NatetheGreat]

When I call The procedure has activesheet

Confused and stressed of London….

When you Call, having clicked the scrollbar you do not actually have an ACTIVE chart!!! You will need to reference the chart by name or add a line to activate it.

Hope that sorts it.

Indeed it has Tony, thanks very much for your help! I now have my first ever functional scrollbar and I am very satisfied Couldn't have done it without you!

 

[Snakehips]

You're welcome. We got there, in the end!!!

 

[spbshaan]

Referred to this thread in 2017.
Thank you Tony and Nate, was scratching my head from a long time!