CODEWITHSUNDEEP
excelvba
Josh Alton
Josh Alton

Reputation: 5

Out if stack space error occurs sometimes on different computers, not sure what the problem is

I want to set 7 different maximum and minimums for 5 spinners depending on a value in another cell.

This code works on multiple computers however on some i run into a out of stack error. Even on the same computer it works some times and not others. Is there a reason for this?

The code is placed into the Microsoft excel objects for the sheet it refers to. And it is repeated 5 times depending on the value of a cell

I have tried adding another elseif, this fixes any errors present however the spin buttons stop working.

Private Sub SpinButton1_Change()
'Barerock spinner
If Worksheets("Model").Range("E4") = 1 Then
    SpinButton1.Max = 4
    SpinButton1.Min = 4

ElseIf Worksheets("Model").Range("E4") = 2 Then
    SpinButton1.Max = 3
    SpinButton1.Min = 3

ElseIf Worksheets("Model").Range("E4") = 3 Then
    SpinButton1.Max = 4
    SpinButton1.Min = 4

ElseIf Worksheets("Model").Range("E4") = 4 Then
    SpinButton1.Max = 3
    SpinButton1.Min = 3

ElseIf Worksheets("Model").Range("E4") = 5 Then
    SpinButton1.Max = 4
    SpinButton1.Min = 4

ElseIf Worksheets("Model").Range("E4") = 6 Then
    SpinButton1.Max = 4
    SpinButton1.Min = 4

End If


SpinButton1.Max = 100 - Range("I9") + Range("D9")
SpinButton1.Min = 0

end sub

Upvotes: 0

Views: 25

Answers (1)

Tim Williams
Tim Williams

Reputation: 166540

Looks like your code is re-triggering the event, so you need some kind of flag to prevent that.

You can try something like this (just guessing at that last part, since your original code looks a bit odd...) 

Private Sub SpinButton1_Change()

    Static inProcess As Boolean, newVal As long

    If inProcess Then Exit Sub '<< don't re-trigger...

    Select Case Worksheets("Model").Range("E4").value' = 1 Then
        Case 1,3,5,6: newVal = 4
        Case 2, 4: newVal = 3
    End Select

    inProcess = True
    If NewVal <> 0 Then
        SpinButton1.Max = newVal 
        SpinButton1.Min = newVal 
    Else
        SpinButton1.Max = 100 - Range("I9") + Range("D9")
        SpinButton1.Min = 0
    End If
    inProcess = False

End sub

Upvotes: 1

Related Questions