Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another row the
textbox updates to 101 with no problem - however if I enter L100 the code
errors
is there a way round this error as sometimes I have to use a letter as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

--
Many Thanks

Sue

Sue,

Prefixing a number with a letter turns it into text so adding 1 to a text
string causes an error. Do it like this by just having the number in C14 and
add the L at runtime

Tb36A.Text = "L" & Range("C14").Text + 1

Mike

"Sue" wrote:

Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another row the
textbox updates to 101 with no problem - however if I enter L100 the code
errors
is there a way round this error as sometimes I have to use a letter as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

--
Many Thanks

Sue

I just noticed that you say you 'sometimes add a letter. Same principle but
add the letter from another range address. It wouldn't matter if that address
was empty

Tb36A.Text = Range("Z1").Text & Range("C14").Text + 1

Mike

"Mike H" wrote:

Sue,

Prefixing a number with a letter turns it into text so adding 1 to a text
string causes an error. Do it like this by just having the number in C14 and
add the L at runtime

Tb36A.Text = "L" & Range("C14").Text + 1

Mike

"Sue" wrote:

Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another row the
textbox updates to 101 with no problem - however if I enter L100 the code
errors
is there a way round this error as sometimes I have to use a letter as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

--
Many Thanks

Sue

Hi Mike

Thanks for quick reply however I forgot to mention I may have 4 or 5
invoices following one another with a single letter prefix for the same
department -- after entering letter L in ("Z1") first time works OK when
changing to next line in the ListBox ready to enter next invoice it gives a
runtime error'13' - Type Mismatch -- can you help.
--
Many Thanks

Sue


"Mike H" wrote:

I just noticed that you say you 'sometimes add a letter. Same principle but
add the letter from another range address. It wouldn't matter if that address
was empty

Tb36A.Text = Range("Z1").Text & Range("C14").Text + 1

Mike

"Mike H" wrote:

Sue,

Prefixing a number with a letter turns it into text so adding 1 to a text
string causes an error. Do it like this by just having the number in C14 and
add the L at runtime

Tb36A.Text = "L" & Range("C14").Text + 1

Mike

"Sue" wrote:

Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another row the
textbox updates to 101 with no problem - however if I enter L100 the code
errors
is there a way round this error as sometimes I have to use a letter as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

--
Many Thanks

Sue

**IF** your values in C14 are **always** two digits long or more, and if
your letter prefix is **always** a single character, you can use this line
in place of the one that caused the error originally...

Tb36A.Text = Left(range("C14", 1) & (Mid(range("C14", 2) + 1)

--
Rick (MVP - Excel)


"Sue" wrote in message
...
Hi Mike

Thanks for quick reply however I forgot to mention I may have 4 or 5
invoices following one another with a single letter prefix for the same
department -- after entering letter L in ("Z1") first time works OK when
changing to next line in the ListBox ready to enter next invoice it gives
a
runtime error'13' - Type Mismatch -- can you help.
--
Many Thanks

Sue


"Mike H" wrote:

I just noticed that you say you 'sometimes add a letter. Same principle
but
add the letter from another range address. It wouldn't matter if that
address
was empty

Tb36A.Text = Range("Z1").Text & Range("C14").Text + 1

Mike

"Mike H" wrote:

Sue,

Prefixing a number with a letter turns it into text so adding 1 to a
text
string causes an error. Do it like this by just having the number in
C14 and
add the L at runtime

Tb36A.Text = "L" & Range("C14").Text + 1

Mike

"Sue" wrote:

Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another
row the
textbox updates to 101 with no problem - however if I enter L100 the
code
errors
is there a way round this error as sometimes I have to use a letter
as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

--
Many Thanks

Sue

Hi Rick

Thanks for your reply

Working late tonight most of the UK snowed up probably stay at a local hotel
so I can get to work tomorrow.

everything meets the If conditions that you asked about and I'm receiving an
error message highlighted in debug at Mid - compile error -- argument not
optional.

Tb36A.Text = Left(range("C14", 1) & (Mid(range("C14", 2) + 1)




--
Many Thanks

Sue


"Rick Rothstein" wrote:

**IF** your values in C14 are **always** two digits long or more, and if
your letter prefix is **always** a single character, you can use this line
in place of the one that caused the error originally...

Tb36A.Text = Left(range("C14", 1) & (Mid(range("C14", 2) + 1)

--
Rick (MVP - Excel)


"Sue" wrote in message
...
Hi Mike

Thanks for quick reply however I forgot to mention I may have 4 or 5
invoices following one another with a single letter prefix for the same
department -- after entering letter L in ("Z1") first time works OK when
changing to next line in the ListBox ready to enter next invoice it gives
a
runtime error'13' - Type Mismatch -- can you help.
--
Many Thanks

Sue


"Mike H" wrote:

I just noticed that you say you 'sometimes add a letter. Same principle
but
add the letter from another range address. It wouldn't matter if that
address
was empty

Tb36A.Text = Range("Z1").Text & Range("C14").Text + 1

Mike

"Mike H" wrote:

Sue,

Prefixing a number with a letter turns it into text so adding 1 to a
text
string causes an error. Do it like this by just having the number in
C14 and
add the L at runtime

Tb36A.Text = "L" & Range("C14").Text + 1

Mike

"Sue" wrote:

Hi

I have a textbox on a Userform and using the code below
when I enter for example 100 and then in the Listbox select another
row the
textbox updates to 101 with no problem - however if I enter L100 the
code
errors
is there a way round this error as sometimes I have to use a letter
as it
denotes the department that is issuing the Invoice.

Private Sub Lb1_Change()
Application.ScreenUpdating = False
Dim invnum As String
Dim iRow As Long
Dim ws As Worksheet
Sheets("Invoice").Select
Tb36A.Text = Range("C14").Text + 1'<<<< errors on this line
Application.ScreenUpdating = True
End Sub

--
Many Thanks

Sue