Required Field for 7 Numeric digits only

[LRay67]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[LRay67]

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\)]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Malik]

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]

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

 

[Rick Rothstein \(MVP - VB\)]

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

 

[Rick Rothstein \(MVP - VB\)]

' 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

 

[Ivyleaf]

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.

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 ShiftAs 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

 

[Ivyleaf]

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.

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.

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.

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 -

 

[Ivyleaf]

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.

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.

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.

There has to be an easier way of doing this......
Not sure if this is "easier", but here is a different approach for youto
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 codebelow
(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 XAs Single, _
                               ByVal YAs 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 -

 

[Rick Rothstein \(MVP - VB\)]

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


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.

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.

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.

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 -

 

[Rick Rothstein \(MVP - VB\)]

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



in message news:[email protected]...