Required Field for 7 Numeric digits only

[LRay67]

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

 

[Malik]

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

 

[johnmanivong]

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

 

[JLGWhiz]

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.

 

[JLGWhiz]

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

 

[JLGWhiz]

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]

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.

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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

 

[LRay67]

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

 

[Rick Rothstein \(MVP - VB\)]

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]

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)

 

[JLGWhiz]

Right click your TextBox1>View 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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[JLGWhiz]

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.

 

[Rick Rothstein \(MVP - VB\)]

What if they type in 7 letters?

Rick

Right click your TextBox1>View 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

 

[JLGWhiz]

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

 

[LRay67]

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]

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

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