• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • Last Modified:

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

0
skillilea
Asked:
skillilea
  • 4
  • 2
1 Solution
 
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
 
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LedigimateCommented:
... and change the Max property back to totRowsd !
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now