+ Reply to Thread
Results 1 to 4 of 4

Scroll bar - to increment btwn MIN and MAX value.

  1. #1
    Registered User
    Join Date
    11-07-2003
    Posts
    26

    Red face Scroll bar - to increment btwn MIN and MAX value.

    Scroll bar - to increment btwn MIN and MAX value.

    Help,

    I don't even know if this is possible;

    Issue:
    I'm looking to put a "scroll bar" in a cell, which could be used to increment between a minimum value, and maximum value (values in other cells). I would like to be able to identify the increment value (sometimes referred to: step size, or index).

    Example: (cells and, value or item)

    A1 = 100,000 (minimum value)
    A2 = 200,000 (maximum value)
    A3 = 5,000 (the 'increment' value, step-size etc.)
    A5 = <the scroll bar>
    A7 = the resulting value between min and max, as adjusted via the 'scroll bar'

    It might be obvious that I want to use the A7 value, as a chart element.

    Thanks for any help you can provide.

    jay

  2. #2
    Tom Ogilvy
    Guest

    RE: Scroll bar - to increment btwn MIN and MAX value.

    Sub Macro2()
    Dim obj As OLEObject
    Dim scrBar As MSForms.ScrollBar
    With Range("A5")
    .Select
    Set obj = ActiveSheet.OLEObjects.Add( _
    ClassType:="Forms.ScrollBar.1", _
    Link:=False, _
    DisplayAsIcon:=False, _
    Left:=.Left, Top:=.Top, _
    Width:=.Width, Height:=.Height)
    End With
    Set scrBar = obj.Object
    obj.LinkedCell = "'" & ActiveSheet.Name & "'!A7"
    scrBar.Min = Range("A1").Value
    scrBar.Max = Range("A2").Value
    scrBar.SmallChange = Range("a3").Value

    End Sub

    If you want to be able to change the values in A1, A2 and A3 after the
    scrollbar is in place and have those changes reflected in the scroll bar, you
    would need to use the worksheet change event to update the properties - they
    can't be linked to those cells.

    --
    Regards,
    Tom Ogilvy


    "jay" wrote:

    >
    > Scroll bar - to increment btwn MIN and MAX value.
    >
    > Help,
    >
    > I don't even know if this is possible;
    >
    > Issue:
    > I'm looking to put a "scroll bar" in a cell, which could be used to
    > increment between a minimum value, and maximum value (values in other
    > cells). I would like to be able to identify the increment value
    > (sometimes referred to: step size, or index).
    >
    > Example: (cells and, value or item)
    >
    > A1 = 100,000 (minimum value)
    > A2 = 200,000 (maximum value)
    > A3 = 5,000 (the 'increment' value, step-size etc.)
    > A5 = <the scroll bar>
    > A7 = the resulting value between min and max, as adjusted via the
    > 'scroll bar'
    >
    > It might be obvious that I want to use the A7 value, as a chart
    > element.
    >
    > Thanks for any help you can provide.
    >
    > jay
    >
    >
    > --
    > jay
    > ------------------------------------------------------------------------
    > jay's Profile: http://www.excelforum.com/member.php...fo&userid=2377
    > View this thread: http://www.excelforum.com/showthread...hreadid=536928
    >
    >


  3. #3
    Registered User
    Join Date
    11-07-2003
    Posts
    26

    Red face Scroll Bar,,,get compiler error, what am I missing ?

    Hello Tom,

    Thank you very much for the code, and I must say I am impressed with the compactness of it.

    However, I get a compiler error of: "User-defined type not defined"
    This error, which gets 'blue' colored by the compiler is:

    scrBar As MSForms.ScrollBar 'note: Dim is in front of this, just wanted to show the 'blued' part herein

    Am I supposed to put something in cell A5 ?

    Am I supposed to add in something under "Tools' -> Reference ?

    Thanks for any input you can provide.

    jay

  4. #4
    Tom Ogilvy
    Guest

    Re: Scroll bar - to increment btwn MIN and MAX value.

    Well yes. You need a reference to the Microsoft Forms 2.0 Library. If you
    manually put a control from the control toolbox toolbar on the worksheet,
    you will have such a reference. Otherwise, in the VBE, go to
    tools=>References and find the above entry and click the checkbox.

    --
    Regards,
    Tom Ogilvy



    "jay" <[email protected]> wrote in message
    news:[email protected]...
    >
    > Hello Tom,
    >
    > Thank you very much for the code, and I must say I am impressed with
    > the compactness of it.
    >
    > However, I get a compiler error of: "User-defined type not defined"
    > This error, which gets 'blue' colored by the compiler is:
    >
    > scrBar As MSForms.ScrollBar 'note: Dim is in front of this, just
    > wanted to show the 'blued' part herein
    >
    > Am I supposed to put something in cell A5 ?
    >
    > Am I supposed to add in something under "Tools' -> Reference ?
    >
    > Thanks for any input you can provide.
    >
    > jay
    >
    >
    > --
    > jay
    > ------------------------------------------------------------------------
    > jay's Profile:

    http://www.excelforum.com/member.php...fo&userid=2377
    > View this thread: http://www.excelforum.com/showthread...hreadid=536928
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1