PDA

View Full Version : [SOLVED] Only numbers in a texbox



mariusc
01-19-2011, 05:21 AM
hello,

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

thank you

GTO
01-19-2011, 05:57 AM
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
01-19-2011, 06:19 AM
Thank you, Mark

It`s exactly what I need!

GTO
01-19-2011, 06:50 AM
Thank you, Mark

It`s exactly what I need!

Happy to help :thumb

mikerickson
01-19-2011, 08:21 AM
This is another way


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

GTO
01-19-2011, 09:24 AM
Neat Mike! I didn't know you could use Chr(0) like that.

mikerickson
01-19-2011, 01:18 PM
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
01-19-2011, 01:33 PM
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
01-19-2011, 02:12 PM
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
01-20-2011, 03:29 AM
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.
:omg2: 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