formulae:Help with UserForm textbox code

I have deigned a UserForm that will allow the user to enter some info
that will be copied to a worksheet when the user clicks the OK button.
There are about a dozen fields the user will enter info to in this
form.

Below is some coding that checks the user's entry in TextBox1, and
compares it to data already in column J of this list, looking for
duplicates. If there is a duplicate, a message box comes up warning the
user this data already exists.

For this example of the problem, assume the user is entering
"M123456" in textbox1. And that this is NOT a duplicate. And, that
the data from TextBox1 will be written to cell J500.

As I'm typing in "M123456", I can see the worksheet in the
background, and I notice the following happening upon hitting each
character:

J500 shows M
J501 shows M1
J502 shows M12
J503 shows M123
J504 shows M1234
J505 shows M12345
J506 shows M123456

I cannot begin to figure out why this is happening. Am I missing some
coding that instructs the macro to wait until the user tabs out of
TextBox1 before looking for duplicates? The current code is shown
below:

With Worksheets("Official list")
If Application.CountIf(.Range("j:j"), TextBox1.Text) > 0 Then
MsgBox "This PO/PL is already on the list. Please edit the existing
record "
TextBox1.Text = Clear

Else

Range("J65536").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text

End If

End With

I have a 2nd question, also.
Concerning the 2nd line of the code above, ending with
........TextBox1.Text)> 0 Then

Some of the user entries will begin with letters, some begin with
numbers. Is using a zero the best way to state any kind of an entry?

Thanks for your help/suggestions.
J.O.

Hi,
This suggests you have your code in "Textbox_Change" event NOT
"Textbox_Exit" event as my previous response. I checked my code and it worked
OK i.e. single entry in Column J.

HTH

"excelnut1954" wrote:

> I have deigned a UserForm that will allow the user to enter some info
> that will be copied to a worksheet when the user clicks the OK button.
> There are about a dozen fields the user will enter info to in this
> form.
>
> Below is some coding that checks the user's entry in TextBox1, and
> compares it to data already in column J of this list, looking for
> duplicates. If there is a duplicate, a message box comes up warning the
> user this data already exists.
>
> For this example of the problem, assume the user is entering
> "M123456" in textbox1. And that this is NOT a duplicate. And, that
> the data from TextBox1 will be written to cell J500.
>
> As I'm typing in "M123456", I can see the worksheet in the
> background, and I notice the following happening upon hitting each
> character:
>
> J500 shows M
> J501 shows M1
> J502 shows M12
> J503 shows M123
> J504 shows M1234
> J505 shows M12345
> J506 shows M123456
>
> I cannot begin to figure out why this is happening. Am I missing some
> coding that instructs the macro to wait until the user tabs out of
> TextBox1 before looking for duplicates? The current code is shown
> below:
>
> With Worksheets("Official list")
> If Application.CountIf(.Range("j:j"), TextBox1.Text) > 0 Then
> MsgBox "This PO/PL is already on the list. Please edit the existing
> record "
> TextBox1.Text = Clear
>
> Else
>
> Range("J65536").End(xlUp)(2).Select
> Application.Selection.Value = TextBox1.Text
>
> End If
>
> End With
>
> I have a 2nd question, also.
> Concerning the 2nd line of the code above, ending with
> ........TextBox1.Text)> 0 Then
>
> Some of the user entries will begin with letters, some begin with
> numbers. Is using a zero the best way to state any kind of an entry?
>
> Thanks for your help/suggestions.
> J.O.
>
>