Coding questions about UserForm TextBox

[excelnut1954]

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.

[voodooJoe]

do you have something in the change event turned on?
perhaps its getting updated as a result of code you're no looking at

- voodooJoe

"excelnut1954" <[email protected]> wrote in message
news:[email protected]...
>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.
>

[excelnut1954]

Thanks for responding.

Well, I had the code originally in the 1st sub Private Sub
CommandButton1_Click()
It works there, but not until the OK button is clicked at the bottom of
the UserForm. The problem here is that I wanted the test to be made for
duplicates made upon leaving TextBox1.

I then put the code in the change event sub Private Sub
TextBox1_Change().
Maybe it's not suppose to be there.... but I tried it anyway.
That when it reacted as I detailed here... see original post.

I suspect it belongs in the 1st sub.... but, if so, there must be other
coding to have it test for the duplicate when the user tabs out of
TextBox1, and not wait until the OK button is clicked at the end.

Does this help explain what I've done? Thanks again.
J.O.

[kounoike]

As toppers has already said, i also think it would be appropriate to be your
code in TextBox1_Exit() which is fired when you hit enter key instead of
TextBox1_Change().

i don't know this is what you want, but what if you try this?

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Worksheets("Official list")
If TextBox1.Text <> "" And _
Not .Range("j:j").Find(TextBox1.Text, LookIn:=xlValues, lookat:=xlWhole,
MatchCase:=False) Is Nothing Then
MsgBox "This PO/PL is already on the list. Please edit the existing Record "
TextBox1.Text = Clear
Cancel = True
Else
Range("J65536").End(xlUp)(2).Select
Application.Selection.Value = TextBox1.Text
Cancel = False
End If
End With
End Sub

keizi

"excelnut1954" <[email protected]> wrote in message
news:[email protected]...
> 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.
>

[excelnut1954]

I copied the sub above. The 1st thing I saw is that these 3 lines are
in red:
If TextBox1.Text <> "" And _
Not .Range("j:j").Find(TextBox1.Text, LookIn:=xlValues,
lookat:=xlWhole,
MatchCase:=False) Is Nothing Then

I'm not getting any error messages. Just those lines in red. What does
that mean? I haven't tried to run anything yet, either.

Also, the part of the code

(ByVal Cancel As MSForms.ReturnBoolean)

what does this mean?
I've always seen the this part in the sub name as (), with nothing
inside. However, I've noticed entries inside the () many times in code
written for other folks here. Is this some kind of documentation? Or is
there a significant meaning of this?

Thanks for reponding.
J.O.

[excelnut1954]

I copied the above sub. The 1st thing I saw is that these 3 lines are
in red:
If TextBox1.Text <> "" And _
Not .Range("j:j").Find(TextBox1.Text, LookIn:=xlValues,
lookat:=xlWhole,
MatchCase:=False) Is Nothing Then

I tried to run it anyway, and that's when I found out there was a
syntax error.
Is there something obvious to anyone? Maybe one of the parenthesis in
the wrong position? I'll fool around with it, but if anyone can come up
with the error, I would appreciate it.

Also, the part of the top line

(ByVal Cancel As MSForms.ReturnBoolean)

what does this mean?

I've always seen the this part in the sub name as (), with nothing
inside. However, I've noticed entries inside the () many times in code
written for other folks here. Is this some kind of documentation? Or is

there a significant meaning of this?


Thanks for reponding, kounoike.
And thanks to anyone else who can help, also.

J.O.

[kounoike]

Hi
i intended that code to be in two lines. so, correct code from Not to Then
to be in one line.

and about (ByVal Cancel As MSForms.ReturnBoolean)
is written in Textbox event in help file(file name is FM20.CHM)
or see The enter and Exit events written in
http://msdn.microsoft.com/library/de...ormsPartII.asp

one more thing, i'm using Excel 2002.

keizi

"excelnut1954" <[email protected]> wrote in message
news:[email protected]...
> I copied the above sub. The 1st thing I saw is that these 3 lines are
> in red:
> If TextBox1.Text <> "" And _
> Not .Range("j:j").Find(TextBox1.Text, LookIn:=xlValues,
> lookat:=xlWhole,
> MatchCase:=False) Is Nothing Then
>
> I tried to run it anyway, and that's when I found out there was a
> syntax error.
> Is there something obvious to anyone? Maybe one of the parenthesis in
> the wrong position? I'll fool around with it, but if anyone can come up
> with the error, I would appreciate it.
>
> Also, the part of the top line
>
> (ByVal Cancel As MSForms.ReturnBoolean)
>
> what does this mean?
>
> I've always seen the this part in the sub name as (), with nothing
> inside. However, I've noticed entries inside the () many times in code
> written for other folks here. Is this some kind of documentation? Or is
>
> there a significant meaning of this?
>
>
> Thanks for reponding, kounoike.
> And thanks to anyone else who can help, also.
>
> J.O.
>

[excelnut1954]

Excellent! This seems to work. I appreciate it, and also the web site.
Collecting all the references I can.
Thanks again

J.O.