Only numbers in a texbox

[mariusc]

hello,

how can i force a user to complete in a textbox userform only numbers - no letters ?

thank you

[GTO]

I'm sure there's an easier way, but try:

Private Sub TextBox1_Change()
Static REX As Object
If REX Is Nothing Then
            Set REX = CreateObject("VBScript.RegExp")
            With REX
        .Global = True
        .Pattern = "[^0-9]"
            End With
    End If
TextBox1.Value = REX.Replace(TextBox1.Value, vbNullString)
End Sub

Hope that helps,

Mark

[mariusc]

Thank you, Mark

It`s exactly what I need!

[GTO]

Thank you, Mark

It`s exactly what I need!

Happy to help

[mikerickson]

This is another way

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    KeyAscii = -KeyAscii * (Chr(KeyAscii) Like "[0-9]")
End Sub

[GTO]

Neat Mike! I didn't know you could use Chr(0) like that.

[mikerickson]

KeyAscii = 0 is not the same as Chr(0), its an internal number.
It (and KeyCode from the KeyDown event) can be fun to work with.

If you have two textboxes, this code will cause an X entered in TextBox1 to be automaticaly moved to TextBox2, along with the focus. The KeyAscii (and its associated effect on a textbox moves from TB1 to TB2, along with the focus.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) 
    If Chr(KeyAscii) = "X" Then
    TextBox2.SetFocus
    End If
End Sub

[macropod]

In a similar vein, the following code ensures only numbers can be input. Any non-numeric keystroke generates a beep and the offending character is deleted.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii > 47 And KeyAscii < 59) Then
 Beep
 KeyAscii = 0
End If
End Sub

Or, if you want to allow the user to input decimal values:

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not (KeyAscii > 47 And KeyAscii < 59) Then
  If KeyAscii = 46 And Len(TextBox1.Text) = 0 Then
    TextBox1.Text = "0"
  ElseIf Not (KeyAscii = 46 And InStr(TextBox1.Text, ".") = 0) Then
    Beep
    KeyAscii = 0
  End If
End If
End Sub

Note: With the second sub, only a single period can be input, and a leading 0 is automatically inserted if the period is the first character input by the user.

[mikerickson]

This will allow the entry of any number, including negatives. The newStr technique can be used in other situations to validate in the KeyPress event.

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Dim newStr As String
    With TextBox1
    newStr = Left(.Text, .SelStart) & Chr(KeyAscii) & Right(.Text, Len(.Text) - .SelStart - .SelLength)
    If Not IsNumeric(newStr & "0") Then
        KeyAscii = 0
    End If
    End With
End Sub

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    TextBox1.Text = Val(Replace(TextBox1.Text, ",", vbNullString))
End Sub

The BeforeUpdate cleans up user entries like "1,1223.45". Used with the Format command, it allows you to force the entry into a particular format, even if the user enters a different format.

[GTO]

KeyAscii = 0 is not the same as Chr(0), its an internal number.

Neat Mike! I didn't know you could use Chr(0) like that.

Can we all pretend I didn't say that?

Thank you again Mike. I missed seeing the paranthesis and have no idea what I was thinking. You were using the -1 produced if the Like resulted in True.

Mark