Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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" wrote in message ups.com... 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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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" wrote in message ups.com... 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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
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" wrote in message ups.com... 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. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Coding questions about UserForm TextBox
Excellent! This seems to work. I appreciate it, and also the web site.
Collecting all the references I can. Thanks again J.O. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Questions concerning VBA coding | Excel Discussion (Misc queries) | |||
TextBox Coding Help | Excel Programming | |||
TextBox Coding Help | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming | |||
Help coding userform | Excel Programming |