ANSI/ASCII problem while restricting userform.textbox entries to numeric

[KolGuyXcel]

I'm using the following code to restrict only numeric entries (zero and positive numbers only, including decimal/fractions) to a TextBox in a UserForm. (Excel VBA - 2007).

Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case vbKey0 To vbKey9, vbKeyBack, vbKeyDelete, _
            vbKeyLeft, vbKeyRight, vbKeyHome, vbKeyEnd
            If KeyAscii = 46 Then If InStr(1, Me.TextBox3.Text, ".") Then KeyAscii = 0
        Case Else
            KeyAscii = 0
            Beep
    End Select
End Sub

I am not using any other event for this TextBox3.
The question is that why is KeyAscii=46 not getting into the "Case Else" block?
Also, I see that this code is also allowing the following 6 special characters in the TextBox3 - "#", "$", "%", "&", "(", "'". Why?

 

[NdNoviceHlp]

Hi KolGuyXcel. Maybe something like this will help. Dave

Private Sub Textbox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii < 33 Or KeyAscii > 41) And (KeyAscii < 45 Or KeyAscii > 57) _
                            Or KeyAscii = 47 Or KeyAscii = 39 Then
MsgBox "Not Allowed " & KeyAscii
KeyAscii = 0
Else
MsgBox "Allowed " & KeyAscii
End If
End Sub

 

[KolGuyXcel]

Hi KolGuyXcel. Maybe something like this will help. Dave

Private Sub Textbox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If (KeyAscii < 33 Or KeyAscii > 41) And (KeyAscii < 45 Or KeyAscii > 57) _
                            Or KeyAscii = 47 Or KeyAscii = 39 Then
MsgBox "Not Allowed " & KeyAscii
KeyAscii = 0
Else
MsgBox "Allowed " & KeyAscii
End If
End Sub

Thanks for the workaround.

 

[Rick Rothstein]

There is a problem with the approach you are using... the user can copy/paste any characters into the TextBox that they want... your code will not restrict that. I have some code to allow digits only into a textbox which could probably be modified for your requirements, but you need to clarify what you mean by fractions for me. Show me an example of an allowable mixed number (whole number coupled with a fraction).

 

[KolGuyXcel]

There is a problem with the approach you are using... the user can copy/paste any characters into the TextBox that they want... your code will not restrict that. I have some code to allow digits only into a textbox which could probably be modified for your requirements, but you need to clarify what you mean by fractions for me. Show me an example of an allowable mixed number (whole number coupled with a fraction).

By fraction I meant 1.2, 2.345 etc. (in decimals) and not the whole number-on-whole number form like ⅔.

And thanks for the heads-up with the copy-paste situation. Never crossed my mind.

 

[NdNoviceHlp]

Never considered the copy paste possibility. I'd also be interested in your solution Rick. Thanks. Dave

 

[Rick Rothstein]

This is something I created a long time ago (back in my compiled Visual Basic days) and modified to work in an Excel UserForm TextBox. With it, a user can only type or paste in digits along with zero or one decimal point... all other characters will be rejected while typing, the entire entry will be rejected for an attempt to paste in an entry containing other characters. Make sure to read the remarks at the beginning of the code and the notes below the code.

' 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. Use large numbers
' if you do not need to restrict either of those lengths.
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 = 6
  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

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, minus or decimal point.

 

[Jaafar Tribak]

zero and positive numbers only, including decimal/fractions

How about this (including the paste situation) ?

Private Sub TextBox1_Change()
    With TextBox1
        If IsNumeric(.Text) Or Len(.Text) = 0 Then
            .Tag = .Text
        Else
            .Text = .Tag
        End If
    End With
End Sub

 

[Akuini]

I'm using the following code to restrict only numeric entries (zero and positive numbers only, including decimal/fractions)

Here's my attemp, using TextBox3_Change instead of TextBox3_KeyPress:

Private Sub TextBox3_Change()
Dim tx As String
With TextBox3
    tx = .Text
        If Len(tx) > 0 Then
            If (IsNumeric(tx) And Not tx Like "*[Ee,+-]*") Or tx = "." Then
                'do nothing
            Else
                .Text = Left(tx, Len(tx) - 1): Beep
            End If
        End If
End With
    
End Sub

Example:
True : "12", "3.44" , ".2"
False: "-10" "2.30E+05", "+2", "2.35.5", "2,000", "23e4", "23a"

Note:
It should also work on copy-paste situation.
IsNumeric function accepts scientific notation such as "2.30E+05", "23e4", so the code reject "E" & "e" in this part Not tx Like "*[Ee,+-]*", (assuming you don't want scientific notation)

 

[Jaafar Tribak]

Forgot to take into account space entries. So my code would be :

Private Sub TextBox1_Change()
    Dim sEntry As String 
    With TextBox1
        sEntry = Replace(.Text, Chr(32), "*")
        If IsNumeric(sEntry) Or Len(sEntry) = 0 Then
            .Tag = sEntry
        Else
            .Text = .Tag
        End If
    End With
End Sub

PS: Should also work regardless of the user Decimal Separator "." or ","