Thank you Mr. Mike, you solved my problem. your code
You are aware that the routine Mike posted is not bullet-proof from several
different angles. Mike pointed out the problem of the misplaced minus sign.
On top of that, the routine allows multiple minus signs and multiple decimal
points to be typed in, and in any combination. Finally, your users will be
able to paste in any bad (non-numeric) data they want (you cannot stop that
using only a KeyPress event).
Here is some code which I have posted in the past that is fully bullet-proof
entry-wise (read the Notes section carefully though as it deals with some
things you have to account for though). The following is general in nature;
there is code below for both entries with digits only and for entries with
decimal points... you will want to look at the second part of my posting
which deals with floating point numbers. Also note that the code for
floating point numbers requires you to specify a maximum number of digits
that can be typed into both the integer part of the number as well as the
decimal part of the number... if you don't want to place a limit on the
user, just set each one to large value.
The routines work quite well and protects the TextBox from pasting
non-numeric entries (the user can paste valid data though) as well as
stopping non-numeric keypresses. By the way, the code is set up for a
TextBox with the default name of TextBox1; change those references to the
name of your actual TextBox.
Rick
For typing digits only in the TextBox
=====================================
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
For typing floating point numbers in the TextBox
=========================================
' Set the maximum number of digits before the
' decimal point in the MaxWhole constant. Set
' the maximum number of digits after the decimal
' point in the MaxDecimal constant.
Dim LastPosition As Long
Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
Const MaxDecimal As Integer = 4
Const MaxWhole As Integer = 2
With TextBox1
If Not SecondTime Then
If .Text Like "*[!0-9.]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like String$(MaxWhole, "#") & "[!.]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
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
NOTES
=========================================
Note that you will have check for the Text property containing a single
character consisting of a decimal point since that must be allowed as a
starting character. If you want to allow negative, as well as positive
values, then use this If statement in place of the second If statement in
the Text1_Change event code above:
If .Text Like "*[!0-9.+-]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]*" Or _
.Text Like "?*[+-]*" Then
Note that now you will have to check the Text property for this one to see
if it contains a single plus sign, minus sign or decimal point by themselves
(that is, test if it is a one-character entry consisting of either a plus
sign, minus sign or decimal point).
I guess I should mention that I'm in the US where the decimal point is a
"dot". If your decimal point is some other characters, then make the obvious
substitutions in the If-Then tests above; or you could query the system for
the decimal point character, store it in a variable and concatenate that
into the string values above in place of the decimal point ("dot") that I
show above. In keeping with the non-APIness of this solution, here is what I
use to get the system's decimal point.
DecimalPointSymbol = Format$(0, ".")
