Data Validation on UserForm

34sweetness

Board Regular
Joined
Oct 7, 2002
Messages
136
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?
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Richie(UK)

MrExcel MVP
Joined
May 17, 2002
Messages
3,329
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

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
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>
 

Forum statistics

Threads
1,144,765
Messages
5,726,173
Members
422,659
Latest member
RGP268

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top