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



## KolGuyXcel (Dec 17, 2022)

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 (Dec 17, 2022)

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 (Dec 17, 2022)

NdNoviceHlp said:


> Hi KolGuyXcel. Maybe something like this will help. Dave
> 
> ```
> Private Sub Textbox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
> ...


Thanks for the workaround.


----------



## Rick Rothstein (Dec 17, 2022)

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 (Dec 17, 2022)

Rick Rothstein said:


> 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 (Dec 17, 2022)

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


----------



## Rick Rothstein (Dec 17, 2022)

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 (Dec 17, 2022)

> 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 (Dec 17, 2022)

KolGuyXcel said:


> 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 (Dec 17, 2022)

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


----------



## KolGuyXcel (Dec 17, 2022)

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 (Dec 18, 2022)

Jaafar Tribak said:


> Forgot to take into account space entries. So my code would be :
> 
> ```
> Private Sub TextBox1_Change()
> ...



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 (Dec 18, 2022)

Domenic said:


> 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 (Dec 18, 2022)

Jaafar Tribak said:


> Good catch _Domenic_ !
> 
> This should do it:
> 
> ...


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 (Dec 18, 2022)

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 (Dec 18, 2022)

Jaafar Tribak said:


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


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 (Dec 18, 2022)

Thanks Rick.


----------



## Akuini (Dec 18, 2022)

Rick Rothstein said:


> 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 (Dec 19, 2022)

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 (Dec 19, 2022)

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


----------

