PDA

View Full Version : userform textbox test for format



kieran989
05-08-2017, 10:24 PM
Hi guys,

I have a userform with a textbox that requires the user to input a $ value.

I need some code for the textbox AfterUpdate event that achieves the following:

1. User should be able to enter the value as either 0.00 or $0.00
2. If user inputs value without "$", AfterUpdate event adds "$" symbol before value
3. Test to ensure value is numeric and doesnt contain any other characters. Msgbox if so.

Thankyou!

Bob Phillips
05-09-2017, 12:35 AM
Private Sub TextBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

On Error GoTo err_handler

With Me

If KeyCode <> 13 And KeyCode <> 9 Then Exit Sub 'only check on return or tab

If Left$(.TextBox1.Text, 1) = "$" Then

If Not IsNumeric(Mid$(.TextBox1.Text, 2, 99)) Then Err.Raise 19999
Else
If Not IsNumeric(.TextBox1.Text) Then Err.Raise 19999
End If
If InStr(.TextBox1.Text, ".") = 0 Then Err.Raise 19999
If Len(.TextBox1.Text) - InStr(.TextBox1.Text, ".") <> 2 Then Err.Raise 19999

Exit Sub

err_handler:
If Err.Number = 19999 Then

MsgBox "invalid number input"

.TextBox1.SelStart = 0
.TextBox1.SelLength = Len(.TextBox1.Text)
KeyCode = 0
.TextBox1.SetFocus
Else

MsgBox Err.Number & ", " & Err.Description
End If
End With

mikerickson
05-09-2017, 10:09 AM
I strongly, strongly, strongly recommend that you put the $ in a label and put that to the left of the text box.

Then you can use code like this to validate the user entry

Private Sub TextBox1_AfterUpdate()
With TextBox1
.Text = Format(.Text, "0.00")
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim newString As String
With TextBox1
newString = Left(.Text, .SelStart) & Chr(KeyAscii) & Mid(.Text, .SelStart + .SelLength + 1)
End With
If Not IsNumeric(newString & "0") Then
Beep
KeyAscii = 0
End If
End Sub