Hi, I'm new to this forum and still quite green when it comes to excel so I was hoping that someone would have a fix for a minor issue I've been having.
I'm trying to use spin buttons to increase or decrease cells (B5:B11, one button per cell), with a range of 1 to 10, however I would like an 8th cell (B12) that decreases by an amount equal to the above cells' increases and that cannot be reduced beyond 0 (Cell B12 just contains "=40-SUM[B5:B11]). The idea is that there are 40 points that can be distributed amongst the 7 cells with the 8th being overflow. I seem to have just about gotten it; this is the code I've worked out for my buttons:
Private Sub SpinButton1_Change()
Dim Smax As Variant
If Range("B12") = 0 Then
Smax = Range("B5")
ElseIf Range("B5") = 10 Then
Smax = 10
Else: Smax = Range("B5") + Range("B12")
End If
SpinButton1.Max = Smax
End Sub
If I distribute all 40 points all of the maximums are set properly to the present value of that cell, however if I then subtract a point from one cell only the maximum for that cell is recalculated and the other cells retain their present maximum despite there being one extra point to distribute. Subtracting a point from one of the other cells causes it to update its new maximum so this is a minor issue and the sheet is usable as is, however I'd like to get it figured out if possible. Any thoughts on how to get each cell to update its maximum without having to first subtract?
I'm trying to use spin buttons to increase or decrease cells (B5:B11, one button per cell), with a range of 1 to 10, however I would like an 8th cell (B12) that decreases by an amount equal to the above cells' increases and that cannot be reduced beyond 0 (Cell B12 just contains "=40-SUM[B5:B11]). The idea is that there are 40 points that can be distributed amongst the 7 cells with the 8th being overflow. I seem to have just about gotten it; this is the code I've worked out for my buttons:
Private Sub SpinButton1_Change()
Dim Smax As Variant
If Range("B12") = 0 Then
Smax = Range("B5")
ElseIf Range("B5") = 10 Then
Smax = 10
Else: Smax = Range("B5") + Range("B12")
End If
SpinButton1.Max = Smax
End Sub
If I distribute all 40 points all of the maximums are set properly to the present value of that cell, however if I then subtract a point from one cell only the maximum for that cell is recalculated and the other cells retain their present maximum despite there being one extra point to distribute. Subtracting a point from one of the other cells causes it to update its new maximum so this is a minor issue and the sheet is usable as is, however I'd like to get it figured out if possible. Any thoughts on how to get each cell to update its maximum without having to first subtract?
