I have a type mismatch and i can't understand why?

I have two textboxes on a form which i deduct from a given amount. Thi
works OK when both boxes have a value in them but just one box entere
& all go's the way of the Pear!?

I had t & t2 decalred as long but tried to get the thing working an
changed them to variants, this just shifted the blame further down
line or 2. My problem i think is due to textbox values being a string
how do i say the input will be a long? or even the cell for tha
matter?



Private Sub CommandButton1_Click()
N = FrmDor1.N
Dim t As Variant: Dim t2 As Variant
Dim note As String
Dim nt As String: Dim nt1 As String


If TextBox1.Value < "" Then
Sheets("Sheet1").Cells(N, 31) = TextBox1.Value
End If
If TextBox2.Value < "" Then
Sheets("Sheet1").Cells(N, 30) = TextBox2.Value
End If
If TextBox3.Value < "" Then
Sheets("Sheet1").Cells(N, 38) = TextBox3.Value
End If

If TextBox4.Value < "" Or TextBox5.Value < "" Then
t = TextBox4.Value 'pack left
t2 = TextBox5.Value 'pack right
If TextBox4.Value = "" Then
nt = ""
Else
nt = t & " left pack,"
End If

If TextBox5.Value = "" Then
nt1 = ""
Else
nt1 = t2 & " right pack,"
End If

note = Sheets("Sheet1").Cells(N, 23)
Sheets("Sheet1").Cells(N, 23) = note & " " & " " &nt& " "&nt1
FrmDor1.TextBox4.Value = note & " " & " " & nt & " " &nt1
'THIS is my problem line
Sheets("Sheet1").Cells(N, 31) = Sheets("Sheet1").Cells_
(N, 11) - t - t2
End If

End Sub

Thanks for even looking at this. Any help will be appreciated.
CA

-----------------------------------------------
~~ Message posted from http://www.ExcelTip.com
~~View and post usenet messages directly from http://www.ExcelForum.com

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements

if NOT ( isnumeric(textbox4.value) and _
isnumeric(textbox4.value)) Then
msgbox "Text boxes must contain numbers!"
exit sub
End If
t =clng(TextBox5.Value) 'pack left
t2 = clng(TextBox5.Value) 'pack right



Patrick Molloy
Microsoft Excel MVP
-----Original Message-----

I have a type mismatch and i can't understand why?

I have two textboxes on a form which i deduct from a
given amount. This
works OK when both boxes have a value in them but just
one box entered
& all go's the way of the Pear!?

I had t & t2 decalred as long but tried to get the thing
working and
changed them to variants, this just shifted the blame
further down a
line or 2. My problem i think is due to textbox values
being a string,
how do i say the input will be a long? or even the cell
for that
matter?



Private Sub CommandButton1_Click()
N = FrmDor1.N
Dim t As Variant: Dim t2 As Variant
Dim note As String
Dim nt As String: Dim nt1 As String


If TextBox1.Value < "" Then
Sheets("Sheet1").Cells(N, 31) = TextBox1.Value
End If
If TextBox2.Value < "" Then
Sheets("Sheet1").Cells(N, 30) = TextBox2.Value
End If
If TextBox3.Value < "" Then
Sheets("Sheet1").Cells(N, 38) = TextBox3.Value
End If

If TextBox4.Value < "" Or TextBox5.Value < "" Then
t = TextBox4.Value 'pack left
t2 = TextBox5.Value 'pack right
If TextBox4.Value = "" Then
nt = ""
Else
nt = t & " left pack,"
End If

If TextBox5.Value = "" Then
nt1 = ""
Else
nt1 = t2 & " right pack,"
End If

note = Sheets("Sheet1").Cells(N, 23)
Sheets("Sheet1").Cells(N, 23) = note & " " & " "
&nt& " "&nt1
FrmDor1.TextBox4.Value = note & " " & " " & nt & " " &nt1
'THIS is my problem line
Sheets("Sheet1").Cells(N, 31) = Sheets("Sheet1").Cells_
(N, 11) - t - t2
End If

End Sub

Thanks for even looking at this. Any help will be
appreciated.
CAA


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from
http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by
step guide to creating financial statements
.

t = clng("0" & Trim(TextBox4.Value)) 'pack left
t2 = clng("0" & trim(TextBox5.Value)) 'pack right

also in your code where you have &, the & should have a space on each side.
if you do VarName&"t"

VarName& could be interpreted as the variable name rather than as a variable
name and concatenation operator.

--
Regards,
Tom Ogilvy

"CAA" wrote in message
...

I have a type mismatch and i can't understand why?

I have two textboxes on a form which i deduct from a given amount. This
works OK when both boxes have a value in them but just one box entered
& all go's the way of the Pear!?

I had t & t2 decalred as long but tried to get the thing working and
changed them to variants, this just shifted the blame further down a
line or 2. My problem i think is due to textbox values being a string,
how do i say the input will be a long? or even the cell for that
matter?



Private Sub CommandButton1_Click()
N = FrmDor1.N
Dim t As Variant: Dim t2 As Variant
Dim note As String
Dim nt As String: Dim nt1 As String


If TextBox1.Value < "" Then
Sheets("Sheet1").Cells(N, 31) = TextBox1.Value
End If
If TextBox2.Value < "" Then
Sheets("Sheet1").Cells(N, 30) = TextBox2.Value
End If
If TextBox3.Value < "" Then
Sheets("Sheet1").Cells(N, 38) = TextBox3.Value
End If

If TextBox4.Value < "" Or TextBox5.Value < "" Then
t = TextBox4.Value 'pack left
t2 = TextBox5.Value 'pack right
If TextBox4.Value = "" Then
nt = ""
Else
nt = t & " left pack,"
End If

If TextBox5.Value = "" Then
nt1 = ""
Else
nt1 = t2 & " right pack,"
End If

note = Sheets("Sheet1").Cells(N, 23)
Sheets("Sheet1").Cells(N, 23) = note & " " & " " &nt& " "&nt1
FrmDor1.TextBox4.Value = note & " " & " " & nt & " " &nt1
'THIS is my problem line
Sheets("Sheet1").Cells(N, 31) = Sheets("Sheet1").Cells_
(N, 11) - t - t2
End If

End Sub

Thanks for even looking at this. Any help will be appreciated.
CAA


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

t = clng("0" & Trim(TextBox4.Value)) 'pack left
t2 = clng("0" & trim(TextBox5.Value)) 'pack right

also in your code where you have &, the & should have a space on each side.
if you do VarName&"t"

VarName& could be interpreted as the variable name rather than as a variable
name and concatenation operator.

--
Regards,
Tom Ogilvy


"CAA" wrote in message
...

I have a type mismatch and i can't understand why?

I have two textboxes on a form which i deduct from a given amount. This
works OK when both boxes have a value in them but just one box entered
& all go's the way of the Pear!?

I had t & t2 decalred as long but tried to get the thing working and
changed them to variants, this just shifted the blame further down a
line or 2. My problem i think is due to textbox values being a string,
how do i say the input will be a long? or even the cell for that
matter?



Private Sub CommandButton1_Click()
N = FrmDor1.N
Dim t As Variant: Dim t2 As Variant
Dim note As String
Dim nt As String: Dim nt1 As String


If TextBox1.Value < "" Then
Sheets("Sheet1").Cells(N, 31) = TextBox1.Value
End If
If TextBox2.Value < "" Then
Sheets("Sheet1").Cells(N, 30) = TextBox2.Value
End If
If TextBox3.Value < "" Then
Sheets("Sheet1").Cells(N, 38) = TextBox3.Value
End If

If TextBox4.Value < "" Or TextBox5.Value < "" Then
t = TextBox4.Value 'pack left
t2 = TextBox5.Value 'pack right
If TextBox4.Value = "" Then
nt = ""
Else
nt = t & " left pack,"
End If

If TextBox5.Value = "" Then
nt1 = ""
Else
nt1 = t2 & " right pack,"
End If

note = Sheets("Sheet1").Cells(N, 23)
Sheets("Sheet1").Cells(N, 23) = note & " " & " " &nt& " "&nt1
FrmDor1.TextBox4.Value = note & " " & " " & nt & " " &nt1
'THIS is my problem line
Sheets("Sheet1").Cells(N, 31) = Sheets("Sheet1").Cells_
(N, 11) - t - t2
End If

End Sub

Thanks for even looking at this. Any help will be appreciated.
CAA


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to
creating financial statements

Thanks for the help.

t = clng("0" & Trim(TextBox4.Value)) 'pack left
t2 = clng("0" & trim(TextBox5.Value)) 'pack right

seemd to tell me there was no object reference, so i sacked it off and
went for the just the CLng option, I assume it means change to long?
This didn't really work either, so instead i put in On error resume
next for both the lines, which worked out OK.
I think it's because there is no value i one of the textboxes, maybe i
should of wrote some code to handle that criterea, but it works now so
maybe not!

Thanks again.
CAA


------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/

~~Now Available: Financial Statements.xls, a step by step guide to creating financial statements