Set properties of scrollbar using VBA code

mahmed1

Well-known Member
Joined
Mar 28, 2009
Messages
2,302
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
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
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
  • 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
 
Upvote 0
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:
Upvote 0
Code:
Private Sub Worksheet_Calculate()
    Shapes("[COLOR="Red"]Scroll Bar 1[/COLOR]").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 ?
 
Upvote 0
Code:
Private Sub Worksheet_Calculate()
    Shapes("[COLOR=red]Scroll Bar 1[/COLOR]").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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,212,933
Messages
6,110,752
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top