Hi,
I have a user form with textboxes on it, in some text boxes, text should be entered, but in some number should be entered, and in some date is required, but How can control the entry, if some one type text instead of number, then it should not be accepted.
NOTE:
Before I have seen such example here, that uses the KeyPress event.
Re: Limit Entry According To Data Type
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
'see Help For ASCII codes
'the following code only permits the entry of letters (CAPITAL Or lowercase)
'anything Else Is reset/ignored
Select Case KeyAscii
Case Is < 32: KeyAscii = 0
Case Is = 32 'space
Case 32 To 47: KeyAscii = 0
Case 48 To 57: KeyAscii = 0 'numbers 0 To 9
Case 58 To 64: KeyAscii = 0
Case 65 To 90: 'capital letters
Case 91 To 96: KeyAscii = 0
Case 97 To 122: 'lowercase letters
Case Else: KeyAscii = 0
End Select
End Sub
the above code only permits text, not number and dates.
How can I use the same thing for [COLOR="Blue"]number[/COLOR] and [COLOR="blue"]dates?[/COLOR]
Re: Limit Entry According To Data Type
Hi,
[vba]Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case Is < 32: KeyAscii = 0
Case Is = 32: KeyAscii = 0 'space
Case 32 To 44: KeyAscii = 0
Case Is = 46: KeyAscii = 0
Case 45, 47 '"-" and "/"
Case 48 To 57 'numbers 0 To 9
Case 58 To 64: KeyAscii = 0
Case 65 To 90: KeyAscii = 0 'capital letters
Case 91 To 96: KeyAscii = 0
Case 97 To 122: KeyAscii = 0 'lowercase letters
Case Else: KeyAscii = 0
End Select
End Sub[/vba]
Re: Limit Entry According To Data Type
A bit unorthordox, but it works:
Private Sub CommandButton1_Click()
Sheet1.Range("G1").Value = dDate
Sheet1.Range("G2").Value = dNumber
Sheet1.Range("G3").Value = dText
End Sub
'DATE
'www.ozgrid.com/forum/showthread.php?t=27977
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo dError
If Mid(TextBox1.Value, 4, 2) > 12 Then
MsgBox "Invalid date, please re-enter", vbCritical
TextBox1.Value = vbNullString
TextBox1.SetFocus
Exit Sub
End If
dDate = DateSerial(Year(Date), Month(Date), Day(Date))
TextBox1.Value = Format(TextBox1.Value, "dd/mm/yyyy")
dDate = TextBox1.Value
Exit Sub
dError:
MsgBox "Invalid date, please re-enter", vbCritical
Me.TextBox1 = ""
End Sub
'TEXT
Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo notNumber
Me.TextBox2.Value = Me.TextBox2.Value * 1
dNumber = Me.TextBox2.Value
Exit Sub
notNumber:
MsgBox "Invalid number, please re-enter", vbCritical
Me.TextBox2 = ""
End Sub
'NUMBER
Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
On Error GoTo myText
Me.TextBox3.Value = Me.TextBox3.Value * 1
MsgBox "Invalid text, please re-enter", vbCritical
Me.TextBox3 = ""
Exit Sub
myText:
dText = Me.TextBox3.Value
End SubRe: Limit Entry According To Data Type
Thanks all, it works well
Re: Limit Entry According To Data Type
Also see about validating UserForm TextBox Controls;
Validating UserForm TextBox to Only Accept Numbers
Validating UserForm TextBox to Only Accept Text
Re: Limit Entry According To Data Type
Also see about how to validate UserForm TextBox Controls;
Validating UserForm TextBox to Only Accept Numbers
Validating UserForm TextBox to Only Accept Text
Don’t have an account yet? Register yourself now and be a part of our community!