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.