Page 1 of 2 12 LastLast
Results 1 to 10 of 18

Set properties of scrollbar using VBA code

This is a discussion on Set properties of scrollbar using VBA code within the Excel Questions forums, part of the Question Forums category; HI Guys, I need to use a cell (I4) to dynamically set the max value of a scrollbar. I am ...

  1. #1
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Set properties of scrollbar using VBA code

    HI Guys,

    I need to use a cell (I4) to dynamically set the max value of a scrollbar.

    I am using a form scroll bar. All i want is if the value of cell i4 changes then the max value of scroll bar should change (i.e what ever is in cell I4). I4 has a formula to determine what the max value should be.

    Many Thanks

  2. #2
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,980

    Default Re: Set properties of scrollbar using VBA code

    • Right-click on the sheet tab that has the scroll bar
    • Select View Code from the pop-up menu
    • Paste the code below in the VBA edit window


    Code:
    Private Sub Worksheet_Calculate()
        Shapes("Scroll Bar 1").DrawingObject.Max = Range("I4").Value
    End Sub
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

  3. #3
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Re: Set properties of scrollbar using VBA code

    Hi Alpha Frog,

    Thank you for your response but i cant get it it to work. What am i doing wrong? The max value does not stop at the value in I4

    My system and scroll bar has just locked after running the code and has frozen.
    Last edited by mahmed1; Oct 22nd, 2011 at 06:42 PM.

  4. #4
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,980

    Default Re: Set properties of scrollbar using VBA code

    Code:
    Private Sub Worksheet_Calculate()
        Shapes("Scroll Bar 1").DrawingObject.Max = Range("I4").Value
    End Sub
    Do you get an error?

    The code assumes your scroll bar is called "Scroll Bar 1". When you right-click on your scroll bar, is that the exact name you see in the Name Box just to the left of the Formula Bar? If not, change the name in the code to match the exact name of your scroll bar.

    What is the formula in I4 ?
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

  5. #5
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Re: Set properties of scrollbar using VBA code

    Quote Originally Posted by AlphaFrog View Post
    Code:
    Private Sub Worksheet_Calculate()
        Shapes("Scroll Bar 1").DrawingObject.Max = Range("I4").Value
    End Sub
    Do you get an error?

    The code assumes your scroll bar is called "Scroll Bar 1". When you right-click on your scroll bar, is that the exact name you see in the Name Box just to the left of the Formula Bar? If not, change the name in the code to match the exact name of your scroll bar.

    What is the formula in I4 ?

    Hi,

    The formula i have in I4 is =IF($E$1,INT(COUNTA($A$2:$A$24)/5),INT(COUNTA($A$2:$A$24)/1)-5)

    The code has run but has frozen my computer and worksheet.

    If it helps the min value for scroll bar is 0
    max is equal to cell I4
    Increment change is 1
    Linked cell for scroll bar is H1
    Linked cell for check box is E1

    Thanks

  6. #6
    MrExcel MVP AlphaFrog's Avatar
    Join Date
    Sep 2009
    Posts
    10,980

    Default Re: Set properties of scrollbar using VBA code

    Try this code instead (delete the previous)

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A2:A24, E1"), Target) Is Nothing Then
            Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
        End If
    End Sub
    If it doesn't work, then what cell(s) does the user manually change that would affect the value of the I4 formula?
    Last edited by AlphaFrog; Oct 22nd, 2011 at 07:01 PM.
    Paste your Excel data to the forum...
    MrExcel HTML Maker or Excel Jeanie

    Surround your pasted VBA code with code tags e.g.;
    [CODE]your VBA code here[/CODE]
    The pound # icon in the Advanced forum editor will apply the code tags around the highlighted text.

  7. #7
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Re: Set properties of scrollbar using VBA code

    Quote Originally Posted by AlphaFrog View Post
    Try this code instead (delete the previous)

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Range("A2:A24, E1"), Target) Is Nothing Then
            Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
        End If
    End Sub
    If it doesn't work, then what cell(s) does the user manually change that would affect the value of the I4 formula?
    Hi for some reason i can't get it to work.

    The range A2:A24 can increase on a daily basis so really i think i should instead have counta(A:A)-1

    The value in I4 will change by 2 things when the data increases in my range (A:A) records get added on a daily basis and 2 by the check box value either changing from false or true (check box value is E1)

    Hope that helps

  8. #8
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,112

    Default Re: Set properties of scrollbar using VBA code

    I don't know what drives your worksheet that may be causing a problem, but AlphaFrog's code works fine for me in the few cases I put together.

  9. #9
    Board Regular
    Join Date
    Mar 2009
    Posts
    1,089

    Default Re: Set properties of scrollbar using VBA code

    Quote Originally Posted by shg View Post
    I don't know what drives your worksheet that may be causing a problem, but AlphaFrog's code works fine for me in the few cases I put together.

    Sorry..don't know what i am doing wrong.

    I have put the code in sheet 1

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2:A24"), Target) Is Nothing Then
    Shapes("Scroll Bar 1").DrawingObject.Max = Range("I4").Value
    End If
    End Sub


    I am using excel 2007

    If i try to run the code from the code screen i get the Macros (Macro name pop up)
    should this happen?

  10. #10
    shg
    shg is offline
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    13,112

    Default Re: Set properties of scrollbar using VBA code

    You can't run it from the Macros dialog because it requires an argument.

    In the VBE, do Tools > Options > General, tick Break in Class Module, and then change A2 and see what happens.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com