|
Required Field for 7 Numeric digits only
I have a required field that can contain only 7 numeric digits within the
textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Hi,
Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
On Mar 30, 8:22*am, LRay67 wrote:
I have a required field that can contain only 7 numeric digits within the textbox. *Also want to put in a message box to alert them that this field must have 7 numeric digits. *Any suggestions on how to write the code behind this? *Thanks Linda Try this Private Sub TextBox1_GotFocus() MsgBox "you have to enter 7 digits" End Sub or Private Sub TextBox1_Enter() MsgBox "you have to have 7 digits" End Sub |
Required Field for 7 Numeric digits only
How are you defining the "field" in the TextBox that will be limited to seven
digits? Will less than seven digits be allowed or must there be exactly seven numeric digits? I am wondering if an input box might be a better approach? Certainly would be easier to control since it allows one to ensure that the user's entry is numeric. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" or UserInput = False Then MsgBox "You must enter numbers only." End If With the Type:=1, VBA automatically displays a message if the entry is not numeric, the If statement wouild then force them to make an entry if they try to bypass by clicking cancel. "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Forgot the length.
RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
This should cover it all, needed to make the loop so they have to respond.
RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Thanks Malik....this works for the message box popping up, but it doesn't
ensure it is only numeric (I can still type in alpha characters). I would like for when the user enters they can only enter 7 numeric digits nothing past that. "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
With the Type:=1, VBA automatically displays a message if the entry is not
numeric, the If statement wouild then force them to make an entry if they try to bypass by clicking cancel. But it allows the user to enter a floating point number which the OP apparently does not want. Rick |
Required Field for 7 Numeric digits only
There are many ways you can approach this problem. Here are two of them...
1) Assuming your TextBox is named TextBox1 and you are using an "OK" CommandButton to accept the entry (and then proceed with the rest of your code), use this CommandButton Click event code (the name of the CommandButton is assumed to be CommandButton1 for this example)... Private Sub CommandButton1_Click() If Not TextBox1.Text Like "#######" Then MsgBox "You must enter 7 digits!" TextBox1.SetFocus End If End Sub 2) You could test the TextBox while it it being typed into and only make the OK button available if there are 7 digits in the TextBox (although you would need a Label next to the TextBox instructing the user to enter exactly 7 digits)... Private Sub TextBox1_Change() CommandButton1.Enabled = (TextBox1.Text Like "#######") End Sub Private Sub TextBox1_Enter() CommandButton1.Enabled = (TextBox1.Text Like "#######") End Sub In either case above, you will need a Cancel button giving the user a way out of your program so that he/she is not trapped in an endless loop assuming the user wants to stop for some reason. Rick "LRay67" wrote in message ... I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
I didn't have my current code when I asked this question. Below is my
current code for the textbox that I need to only allow 7 numeric digits. I would like for the user to enter something, then if it doesn't equal to 7 numberic digits then another message pops up when they leave the field. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Rick Rothstein (MVP - VB)" wrote: There are many ways you can approach this problem. Here are two of them... 1) Assuming your TextBox is named TextBox1 and you are using an "OK" CommandButton to accept the entry (and then proceed with the rest of your code), use this CommandButton Click event code (the name of the CommandButton is assumed to be CommandButton1 for this example)... Private Sub CommandButton1_Click() If Not TextBox1.Text Like "#######" Then MsgBox "You must enter 7 digits!" TextBox1.SetFocus End If End Sub 2) You could test the TextBox while it it being typed into and only make the OK button available if there are 7 digits in the TextBox (although you would need a Label next to the TextBox instructing the user to enter exactly 7 digits)... Private Sub TextBox1_Change() CommandButton1.Enabled = (TextBox1.Text Like "#######") End Sub Private Sub TextBox1_Enter() CommandButton1.Enabled = (TextBox1.Text Like "#######") End Sub In either case above, you will need a Cancel button giving the user a way out of your program so that he/she is not trapped in an endless loop assuming the user wants to stop for some reason. Rick "LRay67" wrote in message ... I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
I didn't have my current code when I posed this question. Below is the code
we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
What kind of TextBox are you using and where is it located at (I get an
error for on your TextBox1.Activate line)? Rick "LRay67" wrote in message ... I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Didn't know there was different textboxes (created by the control toolbar).
This is on a worksheet not on a UserForm. The code is located on the activesheet code itself....not in the workbook. I don't get an error on the TextBox1.Activate (this enables the user to stay in the field until something is entered) "Rick Rothstein (MVP - VB)" wrote: What kind of TextBox are you using and where is it located at (I get an error for on your TextBox1.Activate line)? Rick "LRay67" wrote in message ... I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Right click your TextBox1View Code then paste the snippet below in behind
the TextBox control. It should then reactivate the text box if someone closes it with less than seven digits entered. Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then TextBox1.Activate End If End Sub "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
There isn't different TextBoxes created by the Control Toolbar... it is just
you never mention that is what you were using or that your TextBoxes were directly on the worksheet. The ActiveX TextBox is different from the one that gets placed on a UserForm (there are difference between the events that each can react to and there are differences between available methods as well). In addition to those two type of TextBoxes, there is another one available from the Shapes Toolbar. Anyway, give this LostFocus event code a try (you do not need your KeyPress event code if you use this one)... Private Sub TextBox1_LostFocus() If Not TextBox1.Text Like "#######" Then MsgBox "Please enter exactly 7 digits!" TextBox1.Activate End If End Sub Rick "LRay67" wrote in message ... Didn't know there was different textboxes (created by the control toolbar). This is on a worksheet not on a UserForm. The code is located on the activesheet code itself....not in the workbook. I don't get an error on the TextBox1.Activate (this enables the user to stay in the field until something is entered) "Rick Rothstein (MVP - VB)" wrote: What kind of TextBox are you using and where is it located at (I get an error for on your TextBox1.Activate line)? Rick "LRay67" wrote in message ... I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
You have to be in design mode to access the text box code module. Display
the Control Toolbox toolbar to click the design mode icon. "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
What if they type in 7 letters?
Rick "JLGWhiz" wrote in message ... Right click your TextBox1View Code then paste the snippet below in behind the TextBox control. It should then reactivate the text box if someone closes it with less than seven digits entered. Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then TextBox1.Activate End If End Sub "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
You wanted a message in that, didn't you?
Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then MsgBox "Please Edit Your Entry, Seven Digits Needed" TextBox1.Activate End If End Sub "JLGWhiz" wrote: Right click your TextBox1View Code then paste the snippet below in behind the TextBox control. It should then reactivate the text box if someone closes it with less than seven digits entered. Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then TextBox1.Activate End If End Sub "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
I inserted the If Len(TextBox1.Text) < 7 Then - below the first
TextBox1.Activate code. It does show me the message that I inserted for the field to contain only 7 digits. But it does not stop the user from entering more then 7 and will continue onto the next textbox. I even tried without my code, just inserting what you gave me with the lostfocus. I would like to keep all code pertaining to TextBox1 in the same area. Any Suggestions?? Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate If Len(TextBox1.Text) < 7 Then MsgBox "Accounting Unit Code must contain only 7 Digits" Application.SendKeys ("{BS}") TextBox1.Activate End If Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: Right click your TextBox1View Code then paste the snippet below in behind the TextBox control. It should then reactivate the text box if someone closes it with less than seven digits entered. Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then TextBox1.Activate End If End Sub "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
The only way I know to force them to do it right is to put it in a perpetual
loop until the text box value = 7. See my post with the InputBox and the GoTo statement. It will not let them past that point until Len(TextBox1.Text) = 7. I inserted a suggested method below. You can play with it to suit your purposes. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" RETRY: Application.SendKeys ("{BS}") TextBox1.Activate If Len(TextBox1.Text) < 7 Then MsgBox "Accounting Unit Code must contain only 7 Digits" GoTo RETRY: End If Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "LRay67" wrote: I inserted the If Len(TextBox1.Text) < 7 Then - below the first TextBox1.Activate code. It does show me the message that I inserted for the field to contain only 7 digits. But it does not stop the user from entering more then 7 and will continue onto the next textbox. I even tried without my code, just inserting what you gave me with the lostfocus. I would like to keep all code pertaining to TextBox1 in the same area. Any Suggestions?? Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" RETRY: Application.SendKeys ("{BS}") TextBox1.Activate If Len(TextBox1.Text) < 7 Then MsgBox "Accounting Unit Code must contain only 7 Digits" GoTo RETRY: End If Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: Right click your TextBox1View Code then paste the snippet below in behind the TextBox control. It should then reactivate the text box if someone closes it with less than seven digits entered. Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then TextBox1.Activate End If End Sub "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Malik, after playing with the code you gave me....below is the code that
works with verifying that it has 7 digits and is a numeric filled textbox and the remainder of the code works.. Thanks for your input..... Linda Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Accounting Unit Code must contain 7 Digits only" ' , vbCritical + vbOKOnly Application.SendKeys ("{BS}") Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
That code will accept all of the following as being 7 digits...
1234.56 1,,,,,2 1234e56 1,2,3.5 1,2,3.+ (1,2.3) $12D45$ -12d-34 and many more variations on these. Would you really consider them acceptable input? I haven't tested it (I'll leave that for you to do), but I am pretty sure using the KeyPress event to "proof" your input will **not** stop a user from Copy/Pasting non-digits into the TextBox (and I am pretty sure what they Copy/Paste in will not have to be 7 characters long either). Rick Malik, after playing with the code you gave me....below is the code that works with verifying that it has 7 digits and is a numeric filled textbox and the remainder of the code works.. Thanks for your input..... Linda Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Accounting Unit Code must contain 7 Digits only" ' , vbCritical + vbOKOnly Application.SendKeys ("{BS}") Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Rick, you are correct...played with a couple of the scenarios you gave...and
dang.....have to play some more on the code. Thanks for the catch There has to be an easier way of doing this...... "Rick Rothstein (MVP - VB)" wrote: That code will accept all of the following as being 7 digits... 1234.56 1,,,,,2 1234e56 1,2,3.5 1,2,3.+ (1,2.3) $12D45$ -12d-34 and many more variations on these. Would you really consider them acceptable input? I haven't tested it (I'll leave that for you to do), but I am pretty sure using the KeyPress event to "proof" your input will **not** stop a user from Copy/Pasting non-digits into the TextBox (and I am pretty sure what they Copy/Paste in will not have to be 7 characters long either). Rick Malik, after playing with the code you gave me....below is the code that works with verifying that it has 7 digits and is a numeric filled textbox and the remainder of the code works.. Thanks for your input..... Linda Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Accounting Unit Code must contain 7 Digits only" ' , vbCritical + vbOKOnly Application.SendKeys ("{BS}") Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
IsNumeric is not a very good "number proofer" given what most programmers
expect it to do. Perhaps you will find the following which I have posted in the past to the compiled VB newsgroups (but which applies equally to the VBA world as well)... I usually try and steer people away from using IsNumeric to "proof" supposedly numeric text. Consider this (also see note below): ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)") Most people would not expect THAT to return True. IsNumeric has some "flaws" in what it considers a proper number and what most programmers are looking for. I had a short tip published by Pinnacle Publishing in their Visual Basic Developer magazine that covered some of these flaws. Originally, the tip was free to view but is now viewable only by subscribers.. Basically, it said that IsNumeric returned True for things like -- currency symbols being located in front or in back of the number as shown in my example (also applies to plus, minus and blanks too); numbers surrounded by parentheses as shown in my example (some people use these to mark negative numbers); numbers containing any number of commas before a decimal point as shown in my example; numbers in scientific notation (a number followed by an upper or lower case "D" or "E", followed by a number equal to or less than 305 -- the maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for Hexadecimal, &O or just & in front of the number for Octal). NOTE: ====== In the above example and in the referenced tip, I refer to $ signs and commas and dots -- these were meant to refer to your currency, thousands separator and decimal point symbols as defined in your local settings -- substitute your local regional symbols for these if appropriate. As for your question about checking numbers, here are two functions that I have posted in the past for similar questions..... one is for digits only and the other is for "regular" numbers: Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) 0 And _ Not Value Like "*[!0-9]*" End Function Function IsNumber(ByVal Value As String) As Boolean ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9.]*" And _ Not Value Like "*.*.*" And _ Len(Value) 0 And Value < "." And _ Value < vbNullString End Function Here are revisions to the above functions that deal with the local settings for decimal points (and thousand's separators) that are different than used in the US (this code works in the US too, of course). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String ' Get local setting for decimal point DP = Format$(0, ".") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function I'm not as concerned by the rejection of entries that include one or more thousand's separators, but we can handle this if we don't insist on the thousand's separator being located in the correct positions (in other words, we'll allow the user to include them for their own purposes... we'll just tolerate their presence). Function IsNumber(ByVal Value As String) As Boolean Dim DP As String Dim TS As String ' Get local setting for decimal point DP = Format$(0, ".") ' Get local setting for thousand's separator ' and eliminate them. Remove the next two lines ' if you don't want your users being able to ' type in the thousands separator at all. TS = Mid$(Format$(1000, "#,###"), 2, 1) Value = Replace$(Value, TS, "") ' Leave the next statement out if you don't ' want to provide for plus/minus signs If Value Like "[+-]*" Then Value = Mid$(Value, 2) IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _ Not Value Like "*" & DP & "*" & DP & "*" And _ Len(Value) 0 And Value < DP And _ Value < vbNullString End Function Rick "LRay67" wrote in message ... Rick, you are correct...played with a couple of the scenarios you gave...and dang.....have to play some more on the code. Thanks for the catch There has to be an easier way of doing this...... "Rick Rothstein (MVP - VB)" wrote: That code will accept all of the following as being 7 digits... 1234.56 1,,,,,2 1234e56 1,2,3.5 1,2,3.+ (1,2.3) $12D45$ -12d-34 and many more variations on these. Would you really consider them acceptable input? I haven't tested it (I'll leave that for you to do), but I am pretty sure using the KeyPress event to "proof" your input will **not** stop a user from Copy/Pasting non-digits into the TextBox (and I am pretty sure what they Copy/Paste in will not have to be 7 characters long either). Rick Malik, after playing with the code you gave me....below is the code that works with verifying that it has 7 digits and is a numeric filled textbox and the remainder of the code works.. Thanks for your input..... Linda Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Accounting Unit Code must contain 7 Digits only" ' , vbCritical + vbOKOnly Application.SendKeys ("{BS}") Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
There has to be an easier way of doing this......
Not sure if this is "easier", but here is a different approach for you to consider. The code below will only let the user type in or Copy/Paste digits into the TextBox. If the user attempts to leave the TextBox where there are not 7 digits in it, the user will get a MessageBox and then be returned to the TextBox to correct their entry. So you don't lose what you have, comment out (or copy/save) your existing code and then Copy/Paste all the code below (exactly as it is shown) into the same worksheet code window. Rick Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-9]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If SecondTime = False End Sub Private Sub TextBox1_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) With TextBox1 LastPosition = .SelStart 'Place any other MouseDown event code here End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With TextBox1 LastPosition = .SelStart 'Place any other KeyPress checking code here End With End Sub Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then MsgBox "Please enter exactly 7 digits!" TextBox1.Activate End If End Sub |
Required Field for 7 Numeric digits only
Ray,
I am not writting in detail this code but this can improve the keypress code: Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii 48 And KeyAscii < 58 Then ' You can place rest of your code here Debug.Print KeyAscii Else KeyAscii = 0 ' This will cancel invalid key presses End If End Sub -- Malik "LRay67" wrote: Malik, after playing with the code you gave me....below is the code that works with verifying that it has 7 digits and is a numeric filled textbox and the remainder of the code works.. Thanks for your input..... Linda Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Accounting Unit Code must contain 7 Digits only" ' , vbCritical + vbOKOnly Application.SendKeys ("{BS}") Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
Hi,
I didn;t put much details on this but you might want to use this code. - Solve ISNUMERIC bug - Stop user's invalid characters entry Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii 48 And KeyAscii < 58 Then ' Put your code here Debug.Print KeyAscii Else KeyAscii = 0 End If End Sub ' New Function to handle Public Function MyNumeric(byval MyValue As String) As Boolean Dim LoopCount As Long MyNumeric = False For LoopCount = 1 To Len(MyValue) If Asc(Mid(MyValue, LoopCount, 1)) 48 And Asc(Mid(MyValue, LoopCount, 1)) < 58 Then MyNumeric = True Else MyNumeric = False Exit For End If Next LoopCount End Function Private Sub TextBox1_LostFocus() ' Keep your original code and replace ISNUMERIC with this function MyNumeric (TextBox1.Text) End Sub -- Malik "LRay67" wrote: I didn't have my current code when I posed this question. Below is the code we are using for this particular textbox. I would like the user to be able to enter data into the field and upon tabbing or leaving the field verify that they have 7 numeric digits entered (nothing more or less than that). We have a message stating that they have to enter if nothing is filled in.......Any suggestions Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "JLGWhiz" wrote: This should cover it all, needed to make the loop so they have to respond. RETRY: UserInput = Application.InputBox("Enter an seven numeric digits", _ "NUMERIC ONLY", Type:=1) If UserInput = "" Or UserInput = False Or Len(UserInput) < 7 Then MsgBox "You must enter numbers only." GoTo RETRY: End If "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
I've not tested your code out, but based on prior experience with this
concept... using code only in the KeyPress event will not prevent a user from using Copy/Paste to place any text they want (in this case, non-digits) into the TextBox. Rick "Malik" wrote in message ... Ray, I am not writting in detail this code but this can improve the keypress code: Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii 48 And KeyAscii < 58 Then ' You can place rest of your code here Debug.Print KeyAscii Else KeyAscii = 0 ' This will cancel invalid key presses End If End Sub -- Malik "LRay67" wrote: Malik, after playing with the code you gave me....below is the code that works with verifying that it has 7 digits and is a numeric filled textbox and the remainder of the code works.. Thanks for your input..... Linda Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) If KeyAscii = vbKeyTab Then If TextBox1 = "" Then MsgBox "Please enter Accounting Unit Code (7 Digits)" Application.SendKeys ("{BS}") TextBox1.Activate Exit Sub End If Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Accounting Unit Code must contain 7 Digits only" ' , vbCritical + vbOKOnly Application.SendKeys ("{BS}") Exit Sub End If KeyAscii = 0 TextBox3.Activate End If End Sub "Malik" wrote: Hi, Private Sub TextBox1_LostFocus() Dim IsValid As Boolean IsValid = True ' Checking if TextBox value is 7 digits and all the contents are numeric or not IsValid = ((Len(TextBox1.Text) = 7) And IsNumeric(TextBox1.Text)) If IsValid = False Then MsgBox "Please enter 7 numeric digits only...", vbCritical + vbOKOnly End If End Sub You can improve this by adding this routine at any proper place in your code -- Malik "LRay67" wrote: I have a required field that can contain only 7 numeric digits within the textbox. Also want to put in a message box to alert them that this field must have 7 numeric digits. Any suggestions on how to write the code behind this? Thanks Linda |
Required Field for 7 Numeric digits only
' New Function to handle
Public Function MyNumeric(byval MyValue As String) As Boolean Dim LoopCount As Long MyNumeric = False For LoopCount = 1 To Len(MyValue) If Asc(Mid(MyValue, LoopCount, 1)) 48 And Asc(Mid(MyValue, LoopCount, 1)) < 58 Then MyNumeric = True Else MyNumeric = False Exit For End If Next LoopCount End Function From one of my other posts in this thread (and noting that your MyNumeric function is really a "digits only" function as, unlike the IsNumeric function, it will not handle floating point values), here is a much shorter equivalent function... Function IsDigitsOnly(Value As String) As Boolean IsDigitsOnly = Len(Value) 0 And Not Value Like "*[!0-9]*" End Function Oh, and one point about your function... it reports False if the value passed into it contains a 0... I'm guessing you meant to write =48 rather than 48 in your If-Then statement. Rick |
Required Field for 7 Numeric digits only
Hi,
I know this one has been solved already, but just another slight variation. Note that the code in the userform initialise event is there purely for demonstration... these settings could be set at design time. Also, Rick's method of checking for numbers only is probably neater, but I have always used the method here and have left it in for reference. Dim OldVal As String Private Sub TextBox1_Change() Dim CursorPos As Integer On Error Resume Next If TextBox1.TextLength = 0 Then Exit Sub If Not CStr(Abs(CLng(TextBox1))) = TextBox1 Then Beep With TextBox1 CursorPos = .SelStart - 1 .Text = OldVal If CursorPos .TextLength Then .SelStart = .TextLength Else .SelStart = CursorPos End If End With Else OldVal = TextBox1 End If End Sub Private Sub TextBox1_Enter() TextBox1.ForeColor = vbBlack End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 .ForeColor = IIf(.TextLength < 7, vbRed, vbBlack) End With End Sub Private Sub UserForm_Initialize() With TextBox1 .ControlTipText = "Please enter 7 digits" .MaxLength = 7 End With End Sub Cheers, Ivan. On Mar 31, 9:13*am, "Rick Rothstein \(MVP - VB\)" wrote: There has to be an easier way of doing this...... Not sure if this is "easier", but here is a different approach for you to consider. The code below will only let the user type in or Copy/Paste digits into the TextBox. If the user attempts to leave the TextBox where there are not 7 digits in it, the user will get a MessageBox and then be returned to the TextBox to correct their entry. So you don't lose what you have, comment out (or copy/save) your existing code and then Copy/Paste all the code below (exactly as it is shown) into the same worksheet code window. Rick Dim LastPosition As Long Private Sub TextBox1_Change() * Static LastText As String * Static SecondTime As Boolean * If Not SecondTime Then * * With TextBox1 * * *If .Text Like "*[!0-9]*" Then * * * * Beep * * * * SecondTime = True * * * * .Text = LastText * * * * .SelStart = LastPosition * * * Else * * * * LastText = .Text * * * End If * * End With * End If * SecondTime = False End Sub Private Sub TextBox1_MouseDown(ByVal Button As Integer, _ * * * * * * * * * * * * * * * *ByVal Shift As Integer, _ * * * * * * * * * * * * * * * *ByVal X As Single, _ * * * * * * * * * * * * * * * *ByVal Y As Single) * With TextBox1 * * LastPosition = .SelStart * * 'Place any other MouseDown event code here * End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) * With TextBox1 * * LastPosition = .SelStart * * 'Place any other KeyPress checking code here * End With End Sub Private Sub TextBox1_LostFocus() * If Len(TextBox1.Text) < 7 Then * * MsgBox "Please enter exactly 7 digits!" * * TextBox1.Activate * End If End Sub |
Required Field for 7 Numeric digits only
Sorry,
Only just noticed that it wasn't a user form that the OP was running... please disregard the code above as a whole. Cheers, Ivan. On Apr 1, 1:04*am, Ivyleaf wrote: Hi, I know this one has been solved already, but just another slight variation. Note that the code in the userform initialise event is there purely for demonstration... these settings could be set at design time. Also, Rick's method of checking for numbers only is probably neater, but I have always used the method here and have left it in for reference. Dim OldVal As String Private Sub TextBox1_Change() * * Dim CursorPos As Integer * * On Error Resume Next * * If TextBox1.TextLength = 0 Then Exit Sub * * If Not CStr(Abs(CLng(TextBox1))) = TextBox1 Then * * * * Beep * * * * With TextBox1 * * * * * * CursorPos = .SelStart - 1 * * * * * * .Text = OldVal * * * * * * If CursorPos .TextLength Then * * * * * * * * .SelStart = .TextLength * * * * * * * * Else * * * * * * * * .SelStart = CursorPos * * * * * * End If * * * * End With * * * * Else * * * * OldVal = TextBox1 * * End If End Sub Private Sub TextBox1_Enter() * * TextBox1.ForeColor = vbBlack End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) * * With TextBox1 * * * * .ForeColor = IIf(.TextLength < 7, vbRed, vbBlack) * * End With End Sub Private Sub UserForm_Initialize() * * With TextBox1 * * * * .ControlTipText = "Please enter 7 digits" * * * * .MaxLength = 7 * * End With End Sub Cheers, Ivan. On Mar 31, 9:13*am, "Rick Rothstein \(MVP - VB\)" wrote: There has to be an easier way of doing this...... Not sure if this is "easier", but here is a different approach for you to consider. The code below will only let the user type in or Copy/Paste digits into the TextBox. If the user attempts to leave the TextBox where there are not 7 digits in it, the user will get a MessageBox and then be returned to the TextBox to correct their entry. So you don't lose what you have, comment out (or copy/save) your existing code and then Copy/Paste all the code below (exactly as it is shown) into the same worksheet code window. Rick Dim LastPosition As Long Private Sub TextBox1_Change() * Static LastText As String * Static SecondTime As Boolean * If Not SecondTime Then * * With TextBox1 * * *If .Text Like "*[!0-9]*" Then * * * * Beep * * * * SecondTime = True * * * * .Text = LastText * * * * .SelStart = LastPosition * * * Else * * * * LastText = .Text * * * End If * * End With * End If * SecondTime = False End Sub Private Sub TextBox1_MouseDown(ByVal Button As Integer, _ * * * * * * * * * * * * * * * *ByVal Shift As Integer, _ * * * * * * * * * * * * * * * *ByVal X As Single, _ * * * * * * * * * * * * * * * *ByVal Y As Single) * With TextBox1 * * LastPosition = .SelStart * * 'Place any other MouseDown event code here * End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) * With TextBox1 * * LastPosition = .SelStart * * 'Place any other KeyPress checking code here * End With End Sub Private Sub TextBox1_LostFocus() * If Len(TextBox1.Text) < 7 Then * * MsgBox "Please enter exactly 7 digits!" * * TextBox1.Activate * End If End Sub- Hide quoted text - - Show quoted text - |
Required Field for 7 Numeric digits only
Actually, it does still work with only minor modification. I have also
added 'placeholder' text in this version and an option to cancel out of the data entry process. Note that this code requires the following properties to be set in design mode: ForeColor = &H00808080& (just for aesthetics), MaxLength = 7, and Text = 'Enter 7 digits'. Dim OldVal As String Dim a Private Sub TextBox1_Change() Dim CursorPos As Integer On Error Resume Next If a = vbCancel Then Exit Sub With TextBox1 If .TextLength = 0 Then Exit Sub If Not CStr(Abs(CLng(.Text))) = .Text Then Beep CursorPos = .SelStart - 1 .Text = OldVal If CursorPos .TextLength Then .SelStart = .TextLength Else .SelStart = CursorPos End If Else OldVal = .Text End If End With End Sub Private Sub TextBox1_GotFocus() With TextBox1 If .Text = "Enter 7 digits" Then .Text = vbNullString .ForeColor = vbBlack OldVal = vbNullString End If End With End Sub Private Sub TextBox1_LostFocus() On Error Resume Next With TextBox1 If (Not CStr(Abs(CLng(.Text))) = .Text) Or _ .TextLength < 7 Then a = MsgBox("Please enter a 7 digit code", _ vbRetryCancel, "Incorrect entry") If a = vbCancel Then .ForeColor = &H808080 .Text = "Enter 7 digits" Else .Activate End If End If End With End Sub Ivan. On Apr 1, 1:22*am, Ivyleaf wrote: Sorry, Only just noticed that it wasn't a user form that the OP was running... please disregard the code above as a whole. Cheers, Ivan. On Apr 1, 1:04*am, Ivyleaf wrote: Hi, I know this one has been solved already, but just another slight variation. Note that the code in the userform initialise event is there purely for demonstration... these settings could be set at design time. Also, Rick's method of checking for numbers only is probably neater, but I have always used the method here and have left it in for reference. Dim OldVal As String Private Sub TextBox1_Change() * * Dim CursorPos As Integer * * On Error Resume Next * * If TextBox1.TextLength = 0 Then Exit Sub * * If Not CStr(Abs(CLng(TextBox1))) = TextBox1 Then * * * * Beep * * * * With TextBox1 * * * * * * CursorPos = .SelStart - 1 * * * * * * .Text = OldVal * * * * * * If CursorPos .TextLength Then * * * * * * * * .SelStart = .TextLength * * * * * * * * Else * * * * * * * * .SelStart = CursorPos * * * * * * End If * * * * End With * * * * Else * * * * OldVal = TextBox1 * * End If End Sub Private Sub TextBox1_Enter() * * TextBox1.ForeColor = vbBlack End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) * * With TextBox1 * * * * .ForeColor = IIf(.TextLength < 7, vbRed, vbBlack) * * End With End Sub Private Sub UserForm_Initialize() * * With TextBox1 * * * * .ControlTipText = "Please enter 7 digits" * * * * .MaxLength = 7 * * End With End Sub Cheers, Ivan. On Mar 31, 9:13*am, "Rick Rothstein \(MVP - VB\)" wrote: There has to be an easier way of doing this...... Not sure if this is "easier", but here is a different approach for you to consider. The code below will only let the user type in or Copy/Paste digits into the TextBox. If the user attempts to leave the TextBox where there are not 7 digits in it, the user will get a MessageBox and then be returned to the TextBox to correct their entry. So you don't lose what you have, comment out (or copy/save) your existing code and then Copy/Paste all the code below (exactly as it is shown) into the same worksheet code window. Rick Dim LastPosition As Long Private Sub TextBox1_Change() * Static LastText As String * Static SecondTime As Boolean * If Not SecondTime Then * * With TextBox1 * * *If .Text Like "*[!0-9]*" Then * * * * Beep * * * * SecondTime = True * * * * .Text = LastText * * * * .SelStart = LastPosition * * * Else * * * * LastText = .Text * * * End If * * End With * End If * SecondTime = False End Sub Private Sub TextBox1_MouseDown(ByVal Button As Integer, _ * * * * * * * * * * * * * * * *ByVal Shift As Integer, _ * * * * * * * * * * * * * * * *ByVal X As Single, _ * * * * * * * * * * * * * * * *ByVal Y As Single) * With TextBox1 * * LastPosition = .SelStart * * 'Place any other MouseDown event code here * End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) * With TextBox1 * * LastPosition = .SelStart * * 'Place any other KeyPress checking code here * End With End Sub Private Sub TextBox1_LostFocus() * If Len(TextBox1.Text) < 7 Then * * MsgBox "Please enter exactly 7 digits!" * * TextBox1.Activate * End If End Sub- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
Required Field for 7 Numeric digits only
That seems to work also. One suggestion, though... put an A=0 statement in
the GotFocus event so the TextBox will function correctly if used again **after** the user hits Cancel from the MessageBox. Rick "Ivyleaf" wrote in message ... Actually, it does still work with only minor modification. I have also added 'placeholder' text in this version and an option to cancel out of the data entry process. Note that this code requires the following properties to be set in design mode: ForeColor = &H00808080& (just for aesthetics), MaxLength = 7, and Text = 'Enter 7 digits'. Dim OldVal As String Dim a Private Sub TextBox1_Change() Dim CursorPos As Integer On Error Resume Next If a = vbCancel Then Exit Sub With TextBox1 If .TextLength = 0 Then Exit Sub If Not CStr(Abs(CLng(.Text))) = .Text Then Beep CursorPos = .SelStart - 1 .Text = OldVal If CursorPos .TextLength Then .SelStart = .TextLength Else .SelStart = CursorPos End If Else OldVal = .Text End If End With End Sub Private Sub TextBox1_GotFocus() With TextBox1 If .Text = "Enter 7 digits" Then .Text = vbNullString .ForeColor = vbBlack OldVal = vbNullString End If End With End Sub Private Sub TextBox1_LostFocus() On Error Resume Next With TextBox1 If (Not CStr(Abs(CLng(.Text))) = .Text) Or _ .TextLength < 7 Then a = MsgBox("Please enter a 7 digit code", _ vbRetryCancel, "Incorrect entry") If a = vbCancel Then .ForeColor = &H808080 .Text = "Enter 7 digits" Else .Activate End If End If End With End Sub Ivan. On Apr 1, 1:22 am, Ivyleaf wrote: Sorry, Only just noticed that it wasn't a user form that the OP was running... please disregard the code above as a whole. Cheers, Ivan. On Apr 1, 1:04 am, Ivyleaf wrote: Hi, I know this one has been solved already, but just another slight variation. Note that the code in the userform initialise event is there purely for demonstration... these settings could be set at design time. Also, Rick's method of checking for numbers only is probably neater, but I have always used the method here and have left it in for reference. Dim OldVal As String Private Sub TextBox1_Change() Dim CursorPos As Integer On Error Resume Next If TextBox1.TextLength = 0 Then Exit Sub If Not CStr(Abs(CLng(TextBox1))) = TextBox1 Then Beep With TextBox1 CursorPos = .SelStart - 1 .Text = OldVal If CursorPos .TextLength Then .SelStart = .TextLength Else .SelStart = CursorPos End If End With Else OldVal = TextBox1 End If End Sub Private Sub TextBox1_Enter() TextBox1.ForeColor = vbBlack End Sub Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean) With TextBox1 .ForeColor = IIf(.TextLength < 7, vbRed, vbBlack) End With End Sub Private Sub UserForm_Initialize() With TextBox1 .ControlTipText = "Please enter 7 digits" .MaxLength = 7 End With End Sub Cheers, Ivan. On Mar 31, 9:13 am, "Rick Rothstein \(MVP - VB\)" wrote: There has to be an easier way of doing this...... Not sure if this is "easier", but here is a different approach for you to consider. The code below will only let the user type in or Copy/Paste digits into the TextBox. If the user attempts to leave the TextBox where there are not 7 digits in it, the user will get a MessageBox and then be returned to the TextBox to correct their entry. So you don't lose what you have, comment out (or copy/save) your existing code and then Copy/Paste all the code below (exactly as it is shown) into the same worksheet code window. Rick Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-9]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If SecondTime = False End Sub Private Sub TextBox1_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, _ ByVal Y As Single) With TextBox1 LastPosition = .SelStart 'Place any other MouseDown event code here End With End Sub Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With TextBox1 LastPosition = .SelStart 'Place any other KeyPress checking code here End With End Sub Private Sub TextBox1_LostFocus() If Len(TextBox1.Text) < 7 Then MsgBox "Please enter exactly 7 digits!" TextBox1.Activate End If End Sub- Hide quoted text - - Show quoted text -- Hide quoted text - - Show quoted text - |
validation textbox for numeric values
By "validate text box", do you mean after the user has typed in their value,
but before you attempt to use it? You could check the entry in the Exit event for the TextBox using this function... Function IsSevenDotTwoFloatingPointNumber(ByVal Value As String) As Boolean Const MaxWhole As Integer = 7 Const MaxDecimal As Integer = 2 If Not (Value Like "*[!0-9.]*" Or Value Like "*.*.*" Or _ Value Like "*." & String$(1 + MaxDecimal, "#") Or _ Value Like String$(MaxWhole, "#") & "[!.]*") Then IsSevenDotTwoFloatingPointNumber = True End If End Function and set the events Cancel argument to True if the function returns False. I did have a question about what you wanted to do about leading and/or trailing zeroes. If your intention is to convert the entry to a numerical value after it is "proofed" for being of the right "shape", then leading and trailing zeroes won't affect the entry and can be ignored. If you want to do that, add the following line immediately **before** the If Not statement... Value = Replace(Trim(Replace(Value, "0", " ")), " ", "0") Another possibility is to check the value the user is typing into the TextBox live, as he/she is typing it in. This is doable, but requires a lot more code and some additional after entry, but before using, code checks as well. If that is what you are looking to do, write back to this thread and develop the necessary code to do that for you. Rick "P P" wrote in message ... I want to validate text box such that it should accept only numeric values with upto 2 decimal places and before decimal it should accept less than or equal to 7 digits. cn anyone help me ???? thanks in advance |
| All times are GMT +1. The time now is 10:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com