Combined Spin Button Maximum

[Crankylemming]

Hi

I've been asked to create an interactive worksheet so that users can use "sliders" (by which I'm assuming spin buttons or scroll controls) can distribute £60,000 across up to 6 different sectors, one 'slider' per sector.

I have:

Cells B1:B6 linked to spin buttons 1-6
The £60 (I shrank it down for testing to make using the spin control quicker) figure in cell H1
The combined total of B1:B6 in cell H2
The figure of H1 - H2 in cell H3

Each spin button has a version of this -

Private Sub SpinButton1_Change()
ActiveSheet.SpinButton1.Max = Range("H3").Value
End Sub

What I get is SpinButton1 goes to a maximum of 30; SpinButton2 to a maximum of 15; 3 to 7 etc. I'm obviously doing something wrong, so please could anyone advise how I combine the values on the spin buttons to a single variable maximum?

Thank you in advance.

 

[Eric W]

Welcome to the MrExcel Board.

You're including the active cell in the "used" amount. You'd need to do something like:

Private Sub SpinBUtton1_Change()
    ActiveSheet.SpinButton1.Max = Range("H1").Value - Range("B2").Value - Range("B3").Value - Range("B4").Value - Range("B5").Value - Range("B6").Value
End Sub

 

[mikerickson]

Wouldn't SpinButton1's maximum be set by the Change events for SpinButton2 and SpinButton3, but be unchanged by SpinButton1_Change?

 

[Crankylemming]

Of course I was! So obvious now!

Thank you Eric, that's brilliant - I added the code for the other buttons, leaving out their own range, and all works perfectly.

Thank you for your help!

Steve

Welcome to the MrExcel Board.

You're including the active cell in the "used" amount. You'd need to do something like:

Private Sub SpinBUtton1_Change()
    ActiveSheet.SpinButton1.Max = Range("H1").Value - Range("B2").Value - Range("B3").Value - Range("B4").Value - Range("B5").Value - Range("B6").Value
End Sub

 

[Eric W]

Glad we could help.