Thread: Variable Maximums for Scroll Bars

Greetings,

Apologies for asking about something which is probably quite easy, but I'm a complete newbie to VBA and am still trying to learn.

I have to set four Scroll Bars which are conditional on each other, i.e. when a user scrolls on one of them (a percentage figure), the maximums of the other three immediately recalculate to reflect this - their maximums become 100 (%) - the scrolled value.

I have attached an excel sheet with just these elements, because the total file is quite big and mixed up. I know that the Scroll Bar shapes have to be tweaked through VBA so that their maximum values are set to the four values in Column E (E4:E7), but I have no idea how to do that... Sorry, would be grateful if someone can help.

Thanks.

Assign the same macro, SetScroller , to all the scroll bars.

Private Sub SetScroller(Name As String, Value As Variant)
    ActiveSheet.Shapes(Name).ControlFormat.Max = Value
End Sub

Sub UpdateScrollers()

    Select Case Application.Caller
    Case "Scroll Bar 1"
        SetScroller "Scroll Bar 2", Range("E5")
        SetScroller "Scroll Bar 3", Range("E6")
        SetScroller "Scroll Bar 4", Range("E7")
    Case "Scroll Bar 2"
        SetScroller "Scroll Bar 1", Range("E4")
        SetScroller "Scroll Bar 3", Range("E6")
        SetScroller "Scroll Bar 4", Range("E7")
    Case "Scroll Bar 3"
        SetScroller "Scroll Bar 1", Range("E4")
        SetScroller "Scroll Bar 2", Range("E5")
        SetScroller "Scroll Bar 4", Range("E7")
    Case "Scroll Bar 4"
        SetScroller "Scroll Bar 1", Range("E4")
        SetScroller "Scroll Bar 2", Range("E5")
        SetScroller "Scroll Bar 3", Range("E6")
    End Select
    
End Sub

You can put code in the change event of each control

For scrollbar1

Private Sub ScrollBar1_Change()

    Sheet1.ScrollBar2.Max = Range("E5")
    Sheet1.ScrollBar3.Max = Range("E6")
    Sheet1.ScrollBar4.Max = Range("E7")

End Sub