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
 
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

The WorkSheet_Change event macro is only triggered when you manually change a cell on the worksheet. Pasting values doesn't trigger it.

Change the code to this...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        ActiveSheet.Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
    End If
End Sub
The macro should run when the user makes any manual change to column A


Put the macro below in a standard module and then assign it to the Check Box. This will change the scroll bar when you click on the checkbox.

  • Alt+F11 to open the VBA editor
  • From the VBA menu, select Insert\Module
  • Paste the code below in the VBA edit window.
  • Back in Excel, right-click on the Checkbox and select assign macro
Code:
Sub CB1_Change()
    ActiveSheet.Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
End Sub
 
Upvote 0

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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.

Nothing happens :(
 
Upvote 0
The WorkSheet_Change event macro is only triggered when you manually change a cell on the worksheet. Pasting values doesn't trigger it.

Change the code to this...
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A:A"), Target) Is Nothing Then
        ActiveSheet.Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
    End If
End Sub
The macro should run when the user makes any manual change to column A


Put the macro below in a standard module and then assign it to the Check Box. This will change the scroll bar when you click on the checkbox.

  • Alt+F11 to open the VBA editor
  • From the VBA menu, select Insert\Module
  • Paste the code below in the VBA edit window.
  • Back in Excel, right-click on the Checkbox and select assign macro
Code:
Sub CB1_Change()
    ActiveSheet.Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
End Sub


Thankkkkkkkkkkk You so much Alpha Frog

Thank you SHG and T.Valko who also guided me and helped me also. That works fab.

Love you all

Can you please advise why we needed the second code in a standard module and why could we not link it to the sheet code (I am not complaining just trying to understand). P.s In Essence is both codes treated as if the data were manually input?
i e values in column A input manually and check box changing from true to false?
The code still works fab even if column A haS a formula that is looking at another cell

and finally what does this part of the code do?

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range("A:A"), Target) Is Nothing Then

Many Many thanks...
 
Last edited:
Upvote 0
You're welcome.

Everything we just did was about when to trigger the change to the max value of the scroll bar. Keep that in mind.

We 1st tried to use the Worksheet_Calculate event. That automatically runs when the worksheet recalculates any formula. That could have worked because cell I2 has a formula. But if you have a lot of formulas on the sheet (it seems that you do), then the scroll bar would update when any formula recalcs. Because you have a lot of formulas, that seems to bog down the code and your worksheet appears frozen.

Then we tried using the Worksheet_Change event to trigger off of. This runs when the user manually types in a value on the worksheet. It doesn't trigger when you paste values, or from a formula change, or when you click on a checkbox. The line "If Not Intersect(Range("A:A"), Target) Is Nothing Then" ignores any manual changes to other cells except for column A.

The Worksheet_Change event doesn't trigger when you click on the checkbox even if you have it linked to cell E1. That is why we made the 2nd macro CB1_Change and assigned it to the checkbox. CB1_Change runs when you click on the checkbox.
 
Upvote 0
Hello,

I was wondering how I could get this code to work if I have I have 2 scrollbars, one on each side of a mini table I have set up? I tried repeating the line of code for the right scrollbar thinking it would set both of them but when the code executes I get a run-time error. The modified code I'm using is below (I'm using The ActX scrollbar from the Controls toolbox).

Private Sub OptionButton1_Click()

ActiveSheet.OLEObjects("Inv_Scroll1").Object.Max = Range("AS3").Value
ActiveSheet.OLEObjects("Inv_Scroll2").Object.Max = Range("AS3").Value

End Sub

I hope it is ok by posting in this thread? I checked the forum rules and it didn't say anywhere that I needed to start my own post? If I need to start a whole new post then please advise...thanks!
 
Upvote 0
Yes it's OK to post to an older thread.

What's the error description?

The syntax of your code looks good. I can only guess that your scrollbars are not named Inv_Scroll1 or Inv_Scroll2 or maybe there is text or an error value in AS3
 
Upvote 0
Hi AlphaFrog, sorry so slow getting back to this post but wanted to check back in to let you know that that's exactly what it was! I was missing an "l" in the name of the 2nd scroll bar! All is working fine now! Thanks! :)
 
Upvote 0

Forum statistics

Threads
1,212,929
Messages
6,110,743
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