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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

' 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

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

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