Textbox validation: 1 for integer only, 2 for decimal

[Asher]

Hello,

I have a userfom with multiple textboxes. I need to make sure that one of the textboxes accepts only positive integers and can only allow up to a 4 digit number. Also, 2 of the textboxes can only allow positive decimal numbers with only 2 digits to the right of the decimal point.

Anyone know how to do this and with what event?

I've been looking all over the internet and found only code that isn't quite my situation and pretty confusing or incomplete syntax

Any assistance is much appreciated.

[agibsonsw]

MS Access has a property called an Input Mask which can force the entry of certain digits and characters. Excel doesn't have such a property.
You could use the Change event, which triggers for each character typed in the textbox, but this can become quite complicated. Alternatively,
you could use the BeforeUpdate event and set the Cancel argument to True if the textbox value is not acceptable. For example,

Code: Select all

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Val(TextBox1) <> Int(Abs(TextBox1)) Or Val(TextBox1) > 9999 Then
        MsgBox "Must be a whole number with a maximum of four digits.", vbCritical
        Cancel = True
    End If
End Sub

I will create a version for the decimal number if this will help you? Andy.

P.S. you also need to add the test 'If Not IsNumeric(TextBox1)' to account for someone typing text rather than a number. Andy.

[agibsonsw]

Try the following for the 2nd type of textbox:

Code: Select all

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    If Not IsNumeric(TextBox2) Then
        MsgBox "Must be a number.", vbCritical
        Cancel = True
    ElseIf Val(TextBox2) * 100 <> Int(Abs(TextBox2) * 100) Then
        MsgBox "Must be positive with a maximum of two decimals.", vbCritical
        Cancel = True
    End If
End Sub

although I haven't tested these thoroughly as yet..

[HansV]

As to the first one, you could also use the KeyPress event to suppress unwanted keystrokes:

Code: Select all

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
  If Len(Me.TextBox1) > 3 Or KeyAscii < 48 Or KeyAscii > 57 Then
    KeyAscii = 0 ' setting to 0 effectively cancels the keystroke
  End If
End Sub

[Asher]

Wow folks! This is a lot of good stuff to work with. Thanks so much. I'll try working it in to my code right now!