User Form Calculation (Out of Stack Space)

[phillipsdp]

Hello -

I am having an issue with my user form code. I am receiving an (Out of stack space error) The following code is pushing data entered into the user form to the corresponding excel file in order to calculate values derived from these inputs and then returning the calculated values back to the user form text boxes, which are Listed below as "Reg5, Reg6, and Reg7".

Reg5 is a simple gross profit formula, where Reg2 is the contract price and Reg3 and Reg4 are the relative costs associated with the revenue. Reg6 is a mark up percentage based on the gross profit(Reg5) / Costs(Reg2 + Reg3)). Reg7 is a nested if statement that uses the name from the combobox and the mark up percentage in order to derive the appropriate commission based on the tiered structure that is unique to each employee.

My code is elementary and I'm looking for any solutions or leads in the right direction.

I can share the entire excel workbook as well with any helping parties.

Private Sub ComboBox1_Change()
Sheet2.Range("B9").Value = Me.ComboBox1.Value
Sheet2.Range("d9").Value = Me.Reg2.Value
Sheet2.Range("d10").Value = Me.Reg3.Value
Sheet2.Range("d11").Value = Me.Reg4.Value
Me.Reg5.Value = Sheet2.Range("d12").Value
Me.Reg5.Value = Format(Me.Reg5.Value, "Currency")
Me.Reg6.Value = Sheet2.Range("d13").Value
Me.Reg6.Value = Format(Me.Reg6.Value, "Percent")
Me.Reg7.Value = Sheet2.Range("d14").Value
Me.Reg7.Value = Format(Me.Reg7.Value, "Percent")

End Sub

Private Sub Reg2_Change()
Sheet2.Range("B9").Value = Me.ComboBox1.Value
Sheet2.Range("d9").Value = Me.Reg2.Value
Sheet2.Range("d10").Value = Me.Reg3.Value
Sheet2.Range("d11").Value = Me.Reg4.Value
Me.Reg5.Value = Sheet2.Range("d12").Value
Me.Reg5.Value = Format(Me.Reg5.Value, "Currency")
Me.Reg6.Value = Sheet2.Range("d13").Value
Me.Reg6.Value = Format(Me.Reg6.Value, "Percent")
Me.Reg7.Value = Sheet2.Range("d14").Value
Me.Reg7.Value = Format(Me.Reg7.Value, "Percent")

End Sub

Private Sub Reg3_Change()
Sheet2.Range("B9").Value = Me.ComboBox1.Value
Sheet2.Range("d9").Value = Me.Reg2.Value
Sheet2.Range("d10").Value = Me.Reg3.Value
Sheet2.Range("d11").Value = Me.Reg4.Value
Me.Reg5.Value = Sheet2.Range("d12").Value
Me.Reg5.Value = Format(Me.Reg5.Value, "Currency")
Me.Reg6.Value = Sheet2.Range("d13").Value
Me.Reg6.Value = Format(Me.Reg6.Value, "Percent")
Me.Reg7.Value = Sheet2.Range("d14").Value
Me.Reg7.Value = Format(Me.Reg7.Value, "Percent")

End Sub

Private Sub Reg4_Change()
Sheet2.Range("B9").Value = Me.ComboBox1.Value
Sheet2.Range("d9").Value = Me.Reg2.Value
Sheet2.Range("d10").Value = Me.Reg3.Value
Sheet2.Range("d11").Value = Me.Reg4.Value
Me.Reg5.Value = Sheet2.Range("d12").Value
Me.Reg5.Value = Format(Me.Reg5.Value, "Currency")
Me.Reg6.Value = Sheet2.Range("d13").Value
Me.Reg6.Value = Format(Me.Reg6.Value, "Percent")
Me.Reg7.Value = Sheet2.Range("d14").Value
Me.Reg7.Value = Format(Me.Reg7.Value, "Percent")

End Sub

Private Sub Reg5_Change()
Sheet2.Range("B9").Value = Me.ComboBox1.Value
Sheet2.Range("d9").Value = Me.Reg2.Value
Sheet2.Range("d10").Value = Me.Reg3.Value
Sheet2.Range("d11").Value = Me.Reg4.Value
Me.Reg5.Value = Sheet2.Range("d12").Value
Me.Reg5.Value = Format(Me.Reg5.Value, "Currency")
Me.Reg6.Value = Sheet2.Range("d13").Value
Me.Reg6.Value = Format(Me.Reg6.Value, "Percent")
Me.Reg7.Value = Sheet2.Range("d14").Value
Me.Reg7.Value = Format(Me.Reg7.Value, "Percent")

End Sub

Private Sub Reg6_Change()
Sheet2.Range("B9").Value = Me.ComboBox1.Value
Sheet2.Range("d9").Value = Me.Reg2.Value
Sheet2.Range("d10").Value = Me.Reg3.Value
Sheet2.Range("d11").Value = Me.Reg4.Value
Me.Reg5.Value = Sheet2.Range("d12").Value
Me.Reg5.Value = Format(Me.Reg5.Value, "Currency")
Me.Reg6.Value = Sheet2.Range("d13").Value
Me.Reg6.Value = Format(Me.Reg6.Value, "Percent")
Me.Reg7.Value = Sheet2.Range("d14").Value
Me.Reg7.Value = Format(Me.Reg7.Value, "Percent")

End Sub

 

[StephenCrump]

You will get an "out of stack space" error if your code triggers a cascade of events, e.g. if you change Reg4, your code will trigger the Reg5_Change, Reg6_Change and Reg7_Change events, each of which will trigger other change events, etc etc.

Rather than writing code for change events, perhaps you could add a command button to your UserForm for the user to click once all fields (i.e. ComboBox, TextBoxes etc) had been entered. You could then write code for the CommandButton_Click event to validate and proecess the user input?