Userform has textboxes for input / output, as well as combobox1 & 2 to load data into form and sheet in background. I have been trying to program it so that the user can simply change the text box prices and costs, and it updates on the fly. It works perfectly with only with one input textbox (Price) but when I add the Cost textbox, I get a runtime error 13 message. In my code, if I switch the order of the textbox.value in the initialize, it will read the cost, but not the price. Here is the code.
Private Sub Userform_Initialize()
With Formula_One
.Top = Application.Top + 125 '< change 125 to what u want
.Left = Application.Left + 25 '< change 25 to what u want
End With
ComboBox1.AddItem "Select XX"
ComboBox1.AddItem "A"
ComboBox1.AddItem "B"
ComboBox1.AddItem "C"
ComboBox2.AddItem "Select YY"
ComboBox2.AddItem "AA"
ComboBox2.AddItem "BB"
[COLOR="red"][B]Formula_One.TextBox9.Value = 345
Formula_One.TextBox10.Value = 517.5 '****in this example, this value reads "" during debug****[/B][/COLOR]
End Subhere is the subroutine that calls the data, the error shows up where the rategs variable will have the value of 345, the orategs will show ""; if in the initialize event above I put textbox10.value first, then textbox9.value, orategs will read 517.5, rategs reads "". When I debug, if I go to the initialize and hover over the textbox values one will read a number, and the second will read the "".
ANY IDEAS? :
Private Sub hours_price_cost()
Dim s As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim s1 As String
Dim a As String
Dim b As String
Dim c As String
Dim d As String
Dim a1 As String
Dim b1 As String
Dim c1 As String
Dim d1 As String
Dim rategs As String
Dim orategs As String
Dim ratemw As String
Dim oratemw As String
Dim ratewd As String
Dim oratewd As String
Dim w1 As String
Dim w2 As String
Dim w3 As String
s = "XXXXX"
s1 = "yyyyyy"
Set ws = ActiveWorkbook.Sheets(s)
Set ws2 = ActiveWorkbook.Worksheets(s1)
a = ws.Range("D16")
b = ws.Range("D17")
c = ws.Range("D18")
d = ws.Range("D19")
a1 = ws.Range("E16")
b1 = ws.Range("E17")
c1 = ws.Range("E18")
d1 = ws.Range("E19")
price RATES
[COLOR="red"][B]rategs = Formula_One.TextBox9.value
orategs = Formula_One.TextBox10.value '**** Again, doesn't read the 517.5, just says "" in debug mode[/B][/COLOR]
ratewd = 1
oratewd = 2
ratemw = 3
oratemw = 4
w1 = ws.Range("D28")
w2 = ws.Range("E28")
w3 = ws.Range("F28")
ws.Range("AH17") = ws.Range("q17")
ws.Range("AH18") = ws.Range("D22") * ws.Range("E22") * rategs + ws.Range("D23") * ws.Range("E23") * ratewd + ws.Range("D24") * ws.Range("E24") * ratemw ' + ws.Range("D25") * ws.Range("E25") * ratewd
ws.Range("AH19") = ws.Range("D35")
ws.Range("AH20") = Application.WorksheetFunction.Sum(ws.Range("AM22:AM30"))
ws.Range("AH21") = (a * rategs) + (a1 * orategs) + (b * ratewd) + (b1 * oratewd) + (c * ratemw) + (c1 * oratemw) ' + (d * 255) + (d1 * 382.5)
ws.Range("AH22") = Application.WorksheetFunction.Sum(ws.Range("AH17:AH21"))
ws.Range("AH24") = Application.WorksheetFunction.Sum(ws.Range("AH22:AH23"))
ws.Range("AH26") = ws.Range("AH24") - ws.Range("q14")
ws.Range("Q30") = Application.WorksheetFunction.Sum(ws.Range("AH17:AH21"))
ws.Range("q31") = ws.Range("AH26")
If ws.Range("q26") = 0 Or ws.Range("r26") = 0 Or ws.Range("r22") = 0 Or ws.Range("q22") = 0 Or ws.Range("ah24") = 0 Or ws.Range("AI24") = 0 Then
ws.Range("q27:r27") = 0
ws.Range("q32:r32") = 0
Else
ws.Range("q27") = ws.Range("q26") / ws.Range("q22")
ws.Range("r27") = ws.Range("r26") / ws.Range("r22")
ws.Range("AH27") = ws.Range("AH26") / ws.Range("AH22")
ws.Range("AI27") = ws.Range("AI26") / ws.Range("AI22")
ws.Range("q32") = ws.Range("q31") / ws.Range("Q30")
End If
End Sub