Accessing and controlling a scrollbar in VBA

I am using a scrollbar to move my graph point.  Struggling on how to access it in  VBA.

    'set the scroll
    Dim scroll As msForms.ScrollBar
    Set scroll = dtdash.ScrollBars(1)
    scroll.Max = totRowsd
    scroll.Min = 0
    scroll.LargeChange = 12
    scroll.SmallChange = 1
    scroll.Value = totRowsd - 48  'do a lookup here on current date
    scroll.LinkedCell = "c3"

That is what I have.  dtdash is the worksheet.  The linkedcell property is not showing up and I am trying to make it a reference to another sheet.  
Expert help is appreciated.

Thanks

skillileaAsked:
Who is Participating?
 
LedigimateCommented:
... and change the Max property back to totRowsd !
0
 
patrickabCommented:
Please upload your file.

Patrick
0
 
skillileaAuthor Commented:
Converted from 2007 but you'll get the idea.

I want to be able to control the scrollbar in VBA.

So:

Dim the var
set the var

var.property =


How do I do that?

tnx
Book1.xls
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
LedigimateCommented:
Hi skillilea,

The below code should work for you.  Change the value assigned to the LickedCell property to fit your need.

Ledigimate

P.S.
Merry Christmas!
'set the scroll
    Dim scroll As OLEObject
    Set scroll = Worksheets("DTDash").OLEObjects("Scroll Bar 2")
    scroll.Object.Max = 10
    scroll.Object.Min = 0
    scroll.Object.LargeChange = 12
    scroll.Object.SmallChange = 1
    scroll.Object.Value = totRowsd - 48  'do a lookup here on current date
    scroll.LinkedCell = "CONTROL!H2"

Open in new window

0
 
skillileaAuthor Commented:
Sorry for the delay...

I keep getting Method 'OLEObjects' of object ' _Worksheet failed


0
 
LedigimateCommented:
Do you get that error at compile-time or at runtime?

By the looks of it, the name of a non-existing scollbar may have been used as an argument for the OLEObjects method.

I have created my own workbook with a scrollbar named "Scroll Bar 2", for which the suggested code was tested and worked O.K.  I know for a fact that the scrollbar objects in an Excel worksheet are referenced using the OLEOjects method, as I have tested this and confirmed it to work.

I did notice something strange about your Excel workbook, though.  When one tries to open the Properties window for the scollbar while in design mode, it doesn't open as it normally does with any form control, but the Format Control window opens instead.  This could be an indication of the scroll bar not being accessible through Visual Basic, which is really strange.

Maybe re-inserting the scroll bar and giving it a proper name can help?
0
 
LedigimateCommented:
Oh, yes, of course... methods don't fail at compile-time!  So, ignore that first question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.