strange run time error 13 - type mismatch - textbox.value issue

baxtercavendish

New Member
Joined
Aug 27, 2009
Messages
17
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.

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"][/COLOR][B]Formula_One.TextBox9.Value = 345
Formula_One.TextBox10.Value = 517.5 '****in this example, this value reads "" during debug****[/B][/COLOR]


End Sub
here 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? :

Code:
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

rategs = Formula_One.TextBox9
orategs = Formula_One.TextBox10 
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
 

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Try

Code:
rategs = Formula_One.TextBox9[B][COLOR=Black].value[/COLOR][/B]
orategs = Formula_One.TextBox10[B].value[/B]
 

baxtercavendish

New Member
Joined
Aug 27, 2009
Messages
17
I feel like the error must be occurring in the initialize section, since that value reads "" depending on the order of listing the textbox(9/10).value
 

snowblizz

Well-known Member
Joined
Mar 16, 2009
Messages
1,123
I think your problem might be that you are trying to place integer and double values into a string.

If you put "":s on the numbers would it at least initialize correctly then?
 

baxtercavendish

New Member
Joined
Aug 27, 2009
Messages
17
Code:
Formula_One.TextBox9.Value = "345"
Formula_One.TextBox10.Value = "517.5"
Still gets same error. Any idea on why it reads the first one fine, but the second one ""?
 

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Baxter does your userform_initialize code throw an error if you call it from anywhere else?

Ie what happens if you run

Code:
sub Showfrm()

Formula_One.show

end sub
 

ScottR

Well-known Member
Joined
Apr 17, 2008
Messages
545
Right so it's definitely in the initialization. Weird as there doesn't seem to be anything wrong.

Are you SURE there is a textbox10? I have had weird issues in the past where the VB editor has hiccuped and not "incremented" my controls when adding them to a form.
 

baxtercavendish

New Member
Joined
Aug 27, 2009
Messages
17
There is definitely a TextBox10 (I just double checked) and I know it will read the TextBox10 if it comes before TextBox9 - which it then says is "". It is driving me nuts.

Should I try loading the values of the two boxes outside of the initialize event?
 

Forum statistics

Threads
1,081,902
Messages
5,361,964
Members
400,667
Latest member
cryptomike

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top