VBA to Link ScrollBar Max to Specific Cell

[tquist]

Hi Folks,

I am trying to link the maximum values of a set of scroll bars (across many sheets in the same workbook) to a range of cells set to display the current maximum value. The maximum value for each scrollbar will change each month, necessitating this update. I have almost 100 scroll bars across my workbook, so this would be a prime candidate for automation via VBA.

Here is the deal. Each scroll bar is already linked to a given cell on my "controls" sheet. Two cells to the right of each linked cell is the maximum value that I would like the scroll bar to display. Is there a simple vba code that would assign the each scroll bar's maximum to the value in the cell that is offset 2 columns from each scroll bar's linked cell?

Thank you and please let me know if you need any clarification on my issue. I am using Excel 2007.

Tom

 

[Andrew Poulsom]

Assuming your ScrollBars are from the Control Toolbox, try:

Sub Test()
    Dim Sh As Worksheet
    Dim Obj As OLEObject
    For Each Sh In ThisWorkbook.Worksheets
        For Each Obj In Sh.OLEObjects
            If TypeName(Obj.Object) = "ScrollBar" Then
                Obj.Object.Max = Sh.Range(Obj.LinkedCell).Offset(, 2).Value
            End If
        Next Obj
    Next Sh
End Sub

 

[tquist]

Andrew,

Thanks - this looks promising. However, I'm using Form Controls (not ActiveX) so I don't think that code would work. I should have noted that in my original post - sorry! Could you show the changes to make this work with Form Controls?

Thank you very much.

Tom

 

[Andrew Poulsom]

Much more straightforward:

Sub Test()
    Dim Sh As Worksheet
    Dim Bar As ScrollBar
    For Each Sh In ThisWorkbook.Worksheets
        For Each Bar In Sh.ScrollBars
                Bar.Max = Sh.Range(Bar.LinkedCell).Offset(, 2).Value
        Next Bar
    Next Sh
End Sub

 

[tquist]

Andrew,

Looks great - however I tried it and got an error:

Method 'Range' of object '_Worksheet' failed

Any ideas?

Thanks,
Tom

 

[tquist]

Ah - figured it out. Removing the Sh before the Range:

Bar.Max = Range(Bar.LinkedCell).Offset(, 2).Value

Hopefully this won't have any negative, unintended consequences!

Thanks again for your help, Andrew.

Tom

 

[Andrew Poulsom]

The code worked when I tried it. Are you using named ranges?