VBA - Numeric-only Textbox

[Flashbond]

Here is my version of numeric-only textbox sample. I hope it helps:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
    If KeyAscii = 46 Or KeyAscii = 44 Then
      If TextBox1.Text = "" Then TextBox1.Text = 0
      KeyAscii = Asc(Application.International(xlDecimalSeparator))
      If InStr(TextBox1.Text, Application.International(xlDecimalSeparator)) Then KeyAscii = 0
    Else
      KeyAscii = 0
    End If
  End If
End Sub

 

[bobsan42]

It works when your typing. Blocks all non-numeric symbols, if I get the idea correctly.
But it does not forbid pasting any text, or placing text in by code.

So you need to enhance it further to be reliable, or double check the value before using it elsewhere.
If you just want to be sure that you will get a number out of the textbox (and if it is not on a complex userform) you can use:

x=Application.InputBox("type a number",,,,,,,1)

 

[Flashbond]

Oh, you are right. I just thought the user has a good will. It was only to prevent mistakes. A solid solution could be:

Dim myText As String
Private Sub TextBox1_Change()
  If Not IsNumeric(TextBox1.Text) Then TextBox1.Text = myText
End Sub
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
  myText = TextBox1.Text
End Sub
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If Not (KeyAscii >= 48 And KeyAscii <= 57) Then
    If KeyAscii = 46 Or KeyAscii = 44 Then
      If TextBox1.Text = "" Then TextBox1.Text = 0
      KeyAscii = Asc(Application.International(xlDecimalSeparator))
      If InStr(TextBox1.Text, Application.International(xlDecimalSeparator)) Then KeyAscii = 0
    Else
      KeyAscii = 0
    End If
  End If
End Sub