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?

 

[Domenic]

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 ","

This one I like. It's simple, yet effective. However, it may need a little tweak. It seems to accept a plus sign ( + ) or a minus sign ( - ) after one or more digits.

 

[Jaafar Tribak]

This one I like. It's simple, yet effective. However, it may need a little tweak. It seems to accept a plus sign ( + ) or a minus sign ( - ) after one or more digits.

Good catch Domenic !

This should do it:

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

 

[Rick Rothstein]

Good catch Domenic !

This should do it:

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

Nope, there are still problems. Just 3 that I found right off the bat (then I stopped looking)...

1. Type any number then you can follow that with as many $ signs (my currency symbol... I presume you should substitute yours instead) as you want.

2. Type two or more digits, then move the cursor back one or two positions from the end and type either a "d" or "D".

3. Type any number, then move the cursor to the beginning of the text and type an & character, then type an H followed by as many A's, B's, C's, D's or F's as you like.

You can solve all of these problems by changing your If statement to this...

If (Not sEntry Like "*[!0-9.]*" Or Len(sEntry) = 0&) And (Not sEntry Like "*.*.*") Then

but there will be some issues with editing (try to enter a bad character within the text and the cursor moves to the back of the text). I also note that your code iterates the Change event code over and over again until only a real number remains. I'm guessing you did that on purpose as part of your parsing, but under normal circumstances, that automatic repeated calling of the Change event would be considered bad practice.

 

[Jaafar Tribak]

Thanks Rick. Well spotted.

The following variant is better for eliminating the event code iteration. Obviously, the change event will still be raised when an invalid entry is made.

Private Sub TextBox1_Change()

    Static bDisableEvent As Boolean
    Dim sEntry As String

    If bDisableEvent Then bDisableEvent = False: Exit Sub
    With TextBox1
        sEntry = Replace(.Text, Chr(32&), "*")
        sEntry = Replace(sEntry, Chr(45&), "*")
        sEntry = Replace(sEntry, Chr(43&), "*")
        If Not sEntry Like "*[!0-9.]*" Or Len(sEntry) = 0& Then
            .Tag = sEntry
        Else
            bDisableEvent = True
            .Text = .Tag
        End If
    End With
    
End Sub

2. Type two or more digits, then move the cursor back one or two positions from the end and type either a "d" or "D".

Why would adding a d or D be accepted as numeric ?

 

[Rick Rothstein]

Thanks Rick. Well spotted.

The following variant is better for eliminating the event code iteration. Obviously, the change event will still be raised when an invalid entry is made.

Private Sub TextBox1_Change()

    Static bDisableEvent As Boolean
    Dim sEntry As String

    If bDisableEvent Then bDisableEvent = False: Exit Sub
    With TextBox1
        sEntry = Replace(.Text, Chr(32&), "*")
        sEntry = Replace(sEntry, Chr(45&), "*")
        sEntry = Replace(sEntry, Chr(43&), "*")
        If Not sEntry Like "*[!0-9.]*" Or Len(sEntry) = 0& Then
            .Tag = sEntry
        Else
            bDisableEvent = True
            .Text = .Tag
        End If
    End With
  
End Sub

Why would adding a d or D be accepted as numeric ?

First, look at my posting again... I modified my suggested If statement to stop the user from being able to type multiple decimal points. Second, the reason the d and D work comes from the original BASIC as implemented by Microsoft and upon which Visual Basic and VBA are built. In "olden" days, E-notation was used to create Single values with large exponents and D-notation was used to create Double values with large notation. Somewhere along the line, I think maybe when Singles and Doubles started to be stored in the same-sized memory units, the distinction between the two disappeared. Finally, there is still the editing issue when entering a non-digit, non-decimal point within the text (think of a person mistyping the character) and the cursor goes to the end of the text. I do not believe you can control this with just the change event alone.

 

[Jaafar Tribak]

Thanks Rick.

 

[Akuini]

I also note that your code iterates the Change event code over and over again until only a real number remains. I'm guessing you did that on purpose as part of your parsing, but under normal circumstances, that automatic repeated calling of the Change event would be considered bad practice.

Good point @Rick Rothstein. This also happens in my code in post #9 , so here's another attempt to resolve such issue:

Private Sub TextBox1_Change()
Dim tx As String
Dim flag As Boolean
With TextBox1
    tx = .Text
            Do While toValid(tx) = False
                tx = Left(tx, Len(tx) - 1)
                flag = True
            Loop
    If flag = True Then .Text = tx: Beep
End With
End Sub

Function toValid(tx As String) As Boolean
   toValid = (IsNumeric(tx) And Not tx Like "*[Ee, +-]*") Or tx = "." Or Len(tx) = 0
End Function

 

[Akuini]

Here's another attempt:

Dim oldVal

Private Sub TextBox1_Change()
Dim tx As String

With TextBox1
    tx = .Text
        If (IsNumeric(tx) And Not tx Like "*[!0-9.]*") Or tx = "." Or Len(tx) = 0 Then
            oldVal = .Text
        Else
            .Text = oldVal: Beep
        End If
End With

End Sub

The code only allows number & dot as input:
Example:
True : "12", "3.44" , ".2", "."
False: "-10" "2.30E+05", "+2", "2.35.5", "2,000", "23e4", "23a", "$23", "1,2", "1 2"

 

[mikerickson]

Perhaps

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newValue As String
    With TextBox1
        newValue = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1, 255)
    End With

    If IsNumeric(newValue & "0") Then
        If Val(newValue & "0") < 0 Then KeyAscii = 0
    Else
        KeyAscii = 0
    End If

    If KeyAscii = 0 Then Beep
End Sub