Set properties of scrollbar using VBA code

[mahmed1]

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

 

[AlphaFrog]

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

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

Sub CB1_Change()
    ActiveSheet.Shapes("Scroll Bar 1").DrawingObject.Max = Range("I2").Value
End Sub

 

[mahmed1]

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

 

[mahmed1]

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

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

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

 

[AlphaFrog]

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.

 

[mahmed1]

Thank you so much

much appreciated

 

[Shellecj]

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!

 

[AlphaFrog]

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

 

[Shellecj]

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!