+ Reply to Thread
Results 1 to 5 of 5

Scroll Bar maximum value

  1. #1
    Lee
    Guest

    Scroll Bar maximum value

    I'm designing an interactive chart using a scroll bar object from the Forms
    Toolbar. The data for my chart gets updated daily and I'd like to have the
    Maximum Value of the scroll bar reflect the maximum number of data points.
    Is there a way to do this? The Maximum Value entry does not seem to accept a
    cell value or formula. I appreciate any help you can offer to solve this
    problem.
    Regards,

  2. #2
    Andy Pope
    Guest

    Re: Scroll Bar maximum value

    Hi,

    The Maximum property can not be linked to a cell but you could use a
    cell to store the value and update the scroller if that value changes.

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If IsNumeric(Target.Value) Then
    ActiveSheet.Shapes("Scroll bar 1").ControlFormat.Max _
    = Target.Value
    End If
    End If
    End Sub

    Change cell location and control name to suit.

    Cheers
    Andy

    Lee wrote:
    > I'm designing an interactive chart using a scroll bar object from the Forms
    > Toolbar. The data for my chart gets updated daily and I'd like to have the
    > Maximum Value of the scroll bar reflect the maximum number of data points.
    > Is there a way to do this? The Maximum Value entry does not seem to accept a
    > cell value or formula. I appreciate any help you can offer to solve this
    > problem.
    > Regards,


    --

    Andy Pope, Microsoft MVP - Excel
    http://www.andypope.info

  3. #3
    John Mansfield
    Guest

    RE: Scroll Bar maximum value

    Lee,

    Adding to Andy's comments . . .

    Instead of using the scroll bar in the Forms toolbar, replace it with a
    scroll bar from the Controls toolbar. The Controls toolbar contains controls
    that allow you a lot more flexibility.

    Assume that your interactive chart is currently based on the data in cells
    A1:A5. Add the numbers 3,6,4,5, and 2 into the range A1:A5.

    Go to View -> Toolbox -> Controls Toolbar. Drag the Scroll bar control on
    to your worksheet. Activate the design mode on the controls toolbar. Select
    the scroll bar control by clicking on it once. Right-click on your mouse
    and, while the Scroll bar control is still active, select View Code. Enter
    the following code into the code module.

    Private Sub ScrollBar1_Change()
    Dim Rng As Range
    cnt = 0
    For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    cnt = cnt + 1
    Next Rng
    With ScrollBar1
    .Max = cnt
    .Min = 1
    End With
    ‘Range("C1").Value = ScrollBar1.Value
    End Sub

    If you want to see the results of the Scroll bar count, edit this line in:

    Range("C1").Value = ScrollBar1.Value

    As you add numbers to the end of the range, i.e. into cells A6, A7, A8,
    etc., the maximum scrollbar value will be updated as the control is moved.

    ----
    Regards,
    John Mansfield
    http://www.pdbook.com




    "Lee" wrote:

    > I'm designing an interactive chart using a scroll bar object from the Forms
    > Toolbar. The data for my chart gets updated daily and I'd like to have the
    > Maximum Value of the scroll bar reflect the maximum number of data points.
    > Is there a way to do this? The Maximum Value entry does not seem to accept a
    > cell value or formula. I appreciate any help you can offer to solve this
    > problem.
    > Regards,


  4. #4
    Lee
    Guest

    RE: Scroll Bar maximum value

    Andy and John,
    Thanks a million. That's exactly what I needed. Both ways work in my
    application. I really appreciate the help.
    Regards,
    Lee

    "John Mansfield" wrote:

    > Lee,
    >
    > Adding to Andy's comments . . .
    >
    > Instead of using the scroll bar in the Forms toolbar, replace it with a
    > scroll bar from the Controls toolbar. The Controls toolbar contains controls
    > that allow you a lot more flexibility.
    >
    > Assume that your interactive chart is currently based on the data in cells
    > A1:A5. Add the numbers 3,6,4,5, and 2 into the range A1:A5.
    >
    > Go to View -> Toolbox -> Controls Toolbar. Drag the Scroll bar control on
    > to your worksheet. Activate the design mode on the controls toolbar. Select
    > the scroll bar control by clicking on it once. Right-click on your mouse
    > and, while the Scroll bar control is still active, select View Code. Enter
    > the following code into the code module.
    >
    > Private Sub ScrollBar1_Change()
    > Dim Rng As Range
    > cnt = 0
    > For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    > cnt = cnt + 1
    > Next Rng
    > With ScrollBar1
    > .Max = cnt
    > .Min = 1
    > End With
    > ‘Range("C1").Value = ScrollBar1.Value
    > End Sub
    >
    > If you want to see the results of the Scroll bar count, edit this line in:
    >
    > Range("C1").Value = ScrollBar1.Value
    >
    > As you add numbers to the end of the range, i.e. into cells A6, A7, A8,
    > etc., the maximum scrollbar value will be updated as the control is moved.
    >
    > ----
    > Regards,
    > John Mansfield
    > http://www.pdbook.com
    >
    >
    >
    >
    > "Lee" wrote:
    >
    > > I'm designing an interactive chart using a scroll bar object from the Forms
    > > Toolbar. The data for my chart gets updated daily and I'd like to have the
    > > Maximum Value of the scroll bar reflect the maximum number of data points.
    > > Is there a way to do this? The Maximum Value entry does not seem to accept a
    > > cell value or formula. I appreciate any help you can offer to solve this
    > > problem.
    > > Regards,


  5. #5
    Jon Peltier
    Guest

    Re: Scroll Bar maximum value

    You don't need this:

    Range("C1").Value = ScrollBar1.Value

    Controls Toolbox controls have a LinkedCell property which accepts the address of a
    cell. Use the Properties button on the Controls Toolbox to view the Properties window.

    If I understand your formula, this:

    For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    cnt = cnt + 1
    Next Rng

    can be done without the loop:

    cnt = Range("A" & Rows.Count).End(xlUp).Row

    I like saving code whenever I can.

    - Jon
    -------
    Jon Peltier, Microsoft Excel MVP
    Peltier Technical Services
    Tutorials and Custom Solutions
    http://PeltierTech.com/
    _______


    John Mansfield wrote:

    > Lee,
    >
    > Adding to Andy's comments . . .
    >
    > Instead of using the scroll bar in the Forms toolbar, replace it with a
    > scroll bar from the Controls toolbar. The Controls toolbar contains controls
    > that allow you a lot more flexibility.
    >
    > Assume that your interactive chart is currently based on the data in cells
    > A1:A5. Add the numbers 3,6,4,5, and 2 into the range A1:A5.
    >
    > Go to View -> Toolbox -> Controls Toolbar. Drag the Scroll bar control on
    > to your worksheet. Activate the design mode on the controls toolbar. Select
    > the scroll bar control by clicking on it once. Right-click on your mouse
    > and, while the Scroll bar control is still active, select View Code. Enter
    > the following code into the code module.
    >
    > Private Sub ScrollBar1_Change()
    > Dim Rng As Range
    > cnt = 0
    > For Each Rng In Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    > cnt = cnt + 1
    > Next Rng
    > With ScrollBar1
    > .Max = cnt
    > .Min = 1
    > End With
    > ‘Range("C1").Value = ScrollBar1.Value
    > End Sub
    >
    > If you want to see the results of the Scroll bar count, edit this line in:
    >
    > Range("C1").Value = ScrollBar1.Value
    >
    > As you add numbers to the end of the range, i.e. into cells A6, A7, A8,
    > etc., the maximum scrollbar value will be updated as the control is moved.
    >
    > ----
    > Regards,
    > John Mansfield
    > http://www.pdbook.com
    >
    >
    >
    >
    > "Lee" wrote:
    >
    >
    >>I'm designing an interactive chart using a scroll bar object from the Forms
    >>Toolbar. The data for my chart gets updated daily and I'd like to have the
    >>Maximum Value of the scroll bar reflect the maximum number of data points.
    >>Is there a way to do this? The Maximum Value entry does not seem to accept a
    >>cell value or formula. I appreciate any help you can offer to solve this
    >>problem.
    >>Regards,



+ 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