Accessing and controlling a scrollbar in VBA

Posted on 2009-12-22
Last Modified: 2012-05-08
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.


Question by:skillilea
    LVL 45

    Expert Comment

    Please upload your file.


    Author Comment

    Converted from 2007 but you'll get the idea.

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


    Dim the var
    set the var =

    How do I do that?

    LVL 4

    Expert Comment

    Hi skillilea,

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


    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

    LVL 4

    Accepted Solution

    ... and change the Max property back to totRowsd !

    Author Comment

    Sorry for the delay...

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

    LVL 4

    Expert Comment

    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?
    LVL 4

    Expert Comment

    Oh, yes, of course... methods don't fail at compile-time!  So, ignore that first question.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Threat Intelligence Starter Resources

    Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

    Sometimes we don't want to show zeros in our Excel spreadsheets. This is sometimes most evident in our charts. Look at the chart below, all the zero values are visible. I think that all will agree with the fact that zero values are not looking nice …
    Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
    The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
    This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now