Linking multiple scrollbars based on value

[Scott from Gippsland]

Hi,

I am developing a questionare in excel, and require the users to select an
answer based on a scroll bar, ranging from 1 -100. I have 5 questions, each
with its own scrollbar, which in total need to add up to 100.

Is it possible with VB to enable the scroll bars to automatically adjust so
that when one is changed, the others change accordingly.

Any assistance would be greatly appreciated.

Scott

 

[Leung]

Hi

1st try to use the scroll bar from Control Toolbox rather than Form toolbar,
because it provide more flexibility for programming. After dragging the
first and 2nd toolbars then right-click it > view code, you will see the it
event sub of Scrollbar1_change()

if you want the scrollbar 2 change the same value as scrhollbar 1 change, so
add below code:

Me.ScrollBar2.value = scrollbar1.value

So everytime you change scrollbar1 then scrollbar 2 will change accordingly.

hope this help.

Leung

 

[Scott from Gippsland]

Thanks, this helps in part.

I now need the VB code to go further so that when a scrollbar is modified,
the other change so that the total of all scrollbars is 100.

For example:
If i make scrollbar1 = 40, then scrollbars 2, 3, 4, and 5 need to
automatically recalculate so that each equals 15, making the total of all the
scrollbards equal to 100.

 

[Leung]

Hi

i think _Scroll method is better than _Change method because it won't be
fired when value changed.


Private Sub ScrollBar1_Scroll()
Dim intScore As Integer
intScore = (100 - ScrollBar1.Value) / 4
ScrollBar2.Value = intScore
ScrollBar3.Value = intScore
ScrollBar4.Value = intScore
ScrollBar5.Value = intScore
End Sub

Private Sub ScrollBar2_Scroll()
Dim intScore As Integer
intScore = (100 - ScrollBar2.Value) / 4
ScrollBar1.Value = intScore
ScrollBar3.Value = intScore
ScrollBar4.Value = intScore
ScrollBar5.Value = intScore
End Sub
Private Sub ScrollBar3_Scroll()
Dim intScore As Integer
intScore = (100 - ScrollBar3.Value) / 4
ScrollBar1.Value = intScore
ScrollBar2.Value = intScore
ScrollBar4.Value = intScore
ScrollBar5.Value = intScore
End Sub
Private Sub ScrollBar4_Scroll()
Dim intScore As Integer
intScore = (100 - ScrollBar4.Value) / 4
ScrollBar1.Value = intScore
ScrollBar2.Value = intScore
ScrollBar3.Value = intScore
ScrollBar5.Value = intScore
End Sub
Private Sub ScrollBar5_Scroll()
Dim intScore As Integer
intScore = (100 - ScrollBar5.Value) / 4
ScrollBar1.Locked = True
ScrollBar1.Value = intScore
ScrollBar2.Value = intScore
ScrollBar3.Value = intScore
ScrollBar4.Value = intScore
End Sub

this should work.