VBA Scroll Bar Assignments

NatetheGreat

Active Member
Joined
Nov 18, 2013
Messages
268
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

Code:
    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.

Code:
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!
 
Last edited:

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Nate,
Given your reference to Incremental Change I suspect that you are using a Form scrollbar??? and that you have perhaps changed the default name that would have been Scroll Bar 1 (With spaces) to Scrollbar1 ????


ActiveSheet.Scrollbar1.Value would be valid for an Activex scrollbar of name Scrollbar1

If you do have a Form scrollbar then try ActiveSheet.ScrollBars("Scrollbar1").Value

Hope that helps.
 
Upvote 0
=Snakehips;3650129]Nate,
Given your reference to Incremental Change I suspect that you are using a Form scrollbar??? and that you have perhaps changed the default name that would have been Scroll Bar 1 (With spaces) to Scrollbar1 ????


ActiveSheet.Scrollbar1.Value would be valid for an Activex scrollbar of name Scrollbar1

If you do have a Form scrollbar then try ActiveSheet.ScrollBars("Scrollbar1").Value

Hope that helps.

Hi There Tony,

Thank you for the tip. I can confirm that what I am using is indeed a Form Scrollbar and not an Activex Scrollbar ( What is the difference ?). However when I amend my code to be how you say it seems to be failing with something else. Perhaps I am still missing some syntax somewhere. Greatly appreciate the help! there is nothing worse than being stuck on a project with work and having no resource or manner to progress when googling gets so far. Thank you

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

Failing on Select Case ActiveSheet.Scrollbars("Scrollbar1").Value with

"Unable to get the Scrollbars property of the worksheet class"
 
Upvote 0
To get the value of a scrollbar from the Forms menu, the .ControlFormat object is used
Code:
MsgBox ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value
 
Upvote 0
To get the value of a scrollbar from the Forms menu, the .ControlFormat object is used
Code:
MsgBox ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value

Hi there. Thanks so much for the response. I actually have now changed my code to look like this as per your suggestion.

Code:
Sub Scrollbar1_Change()
MsgBox ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value
'Select Case ActiveSheet.ScrollBars("Scrollbar1").Value
Case "0"
Call FullChartDataRange
Case "1"
Call QuarterChartDataRange
Case "2"
Call HalfChartDataRange
End Select
End Sub

But now It is failing on the Select Case with "Compile error: Case without Select Case"

If I remove the ' on the Select Case line then it fails on the Msgbox ActiveSheet line with
"item with the specified name wasn't found"

It doesn't appear to be recognising the syntax ?

Tks

Nate
 
Last edited:
Upvote 0
Nate,

I believe either of the two code lines below will work provided that you have the correct Name for the scrollbar.

Code:
Select Case ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value
'or
Select Case ActiveSheet.ScrollBars("Scroll Bar 1").Value
As I said in my first post 'Scroll Bar 1' would be a default name for a form scrollbar. I used 'Scrollbar1' as it was the name you originally posted and I assumed you had changed it. With the scrollbar selected, the current name appears in the Name Box, under the ribbon, left.
 
Last edited:
Upvote 0
Replace the "MsgBox" with "Select Case"

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

is now yielding "the item with the specified name wasn't found"

Have I still missed something ?
 
Upvote 0
Nate,

I believe either of the two code lines below will work provided that you have the correct Name for the scrollbar.

Code:
Select Case ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value
'or
Select Case ActiveSheet.ScrollBars("Scroll Bar 1").Value
As I said in my first post 'Scroll Bar 1' would be a default name for a form scrollbar. I used 'Scrollbar1' as it was the name you originally posted and I assumed you had changed it. With the scrollbar selected, the current name appears in the Name Box, under the ribbon, left.


HI Tony,

Now when I do

Code:
Sub Scrollbar1_Change()
Select Case ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value
Case "0"
Call FullChartDataRange
Case "1"
Call QuarterChartDataRange
Case "2"
Call HalfChartDataRange
End Select
End Sub

It fails with the item with the specified name wasn't found. I am really confused. How do I double check the name of the scroll bar for this activesheet.shapes reference ?
 
Upvote 0
HI Tony,

Now when I do

Code:
Sub Scrollbar1_Change()
Select Case ActiveSheet.Shapes("Scroll Bar 1").ControlFormat.Value
Case "0"
Call FullChartDataRange
Case "1"
Call QuarterChartDataRange
Case "2"
Call HalfChartDataRange
End Select
End Sub

It fails with the item with the specified name wasn't found. I am really confused. How do I double check the name of the scroll bar for this activesheet.shapes reference ?

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.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top