Data Validation on UserForm

[34sweetness]

How can I make sure that only "numbers" are input into a textbox on a UserForm?
Also: only uppercase text that is a maximum of 3 letters?
Also: How do I require an entry in a textbox?

 

[Richie(UK)]

Hi,

You can use the IsNumeric function to test for entry of numbers. The following example is from the VBE Help files:

Dim MyVar, MyCheck
MyVar = "53" ' Assign value.
MyCheck = IsNumeric(MyVar) ' Returns True.

HTH

 

[Mudface]

This will validate TextBox1 for 3 letters and convert them to uppercase and TextBox2 for numbers only. Only use the last procedure if you just want integers to be input: -
<pre>
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Len(TextBox1) <> 3 Then
Cancel = True
MsgBox "3 letters only"
End If

End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
Case Is < 32
Exit Sub
Case 65 To 90, 97 To 122
Exit Sub
Case Else
KeyAscii = 0
End Select

End Sub

Private Sub TextBox1_Change()

With TextBox1
.Text = UCase(.Text)
End With

End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

If Not IsNumeric(TextBox2) Or TextBox2 = "" Then
Cancel = True
MsgBox "You must enter a number in this box"
End If

End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
' Use only if integers are to be input

Select Case KeyAscii
Case Is < 32
Case 48 To 57
Case Else
KeyAscii = 0
End Select

End Sub
</pre>