use only numbers and '/'

[ranswrt]

I have a textbox in a userform that I want to only accept numbers and '/' to
enter dates. How do I do that?
Thanks

 

[Bob Phillips]

Here are a couple of routines that I use that you can play about with, they
should be clear

private Sub TextBox2_KeyPress(ByVal KeyAscii As _
MSForms.ReturnInteger)
Dim cDelim As Long
cDelim = Len(TextBox2.Text) - Len(Replace
(TextBox2.Text, "/", ""))
Select Case KeyAscii
Case Asc("0") To Asc("9"): 'OK
Case Asc("/"):
If cDelim = 2 Then
KeyAscii = 0
Else
cDelim = cDelim + 1
End If
Case Else: KeyAscii = 0
End Select
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 45 'negative
If Len(Trim(TextBox1.Text)) > 1 Then
Beep
KeyAscii = 0
End If
Case 46 'period
If InStr(TextBox1.Text, ".") > 0 Then
Beep
KeyAscii = 0
End If
Case 48 To 57 'numbers
If InStr(TextBox1.Text, ".") > 0 Then
If Len(TextBox1.Text) > InStr(TextBox1.Text, ".") + 1 Then
Beep
KeyAscii = 0
End If
End If
Case Else 'Discard anything else
Beep
KeyAscii = 0
End Select
End Sub

 

[Keith74]

Have a look at the keypress event for that textbox.

 

[Office_Novice]

Maybe this will do it

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim B
B = TextBox1.Value
If B <> Format(TextBox1.Value, "mm/dd/yy") Then
Cancel = True And MsgBox("Format Must Be mm/dd/yy", vbCritical)
End If
End Sub

 

[Rick Rothstein \(MVP - VB\)]

I have a textbox in a userform that I want to only accept numbers

and '/' to enter dates. How do I do that?

You can use the code at the bottom of my posting to do what you asked (it
will also stop the user from pasting in text containing characters other
than digits and the slash character). You will still have to validate the
entry after the user has finished typing his/her entry to make sure it is a
valid date (you can use the IsDate function for this).

Just out of curiosity, have you considered using one of the drop-down
calendar controls to take your date input (the input mechanism is much more
natural for the user and it foolproof date-entry-wise)? I use the "Microsoft
Date and Time Picker Control" for this purpose. You can add it to your
UserForm's Toolbox by right-clicking the Toolbox and clicking on "Additional
Controls", then put a checkmark next to the "Microsoft Date and Time Picker
Control" item and click OK. Try it... I think you will like it.

Rick

'***********BEGIN PASTE***********
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
With TextBox1
If Not SecondTime Then
If .Text Like "*[!0-9/]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
LastPosition = TextBox1.SelStart
End Sub
'***********END PASTE***********

 

[ranswrt]

Thanks for the info for a calender I have wanting to use one of those.

I have a textbox in a userform that I want to only accept numbers
and '/' to enter dates. How do I do that?

You can use the code at the bottom of my posting to do what you asked (it
will also stop the user from pasting in text containing characters other
than digits and the slash character). You will still have to validate the
entry after the user has finished typing his/her entry to make sure it is a
valid date (you can use the IsDate function for this).

Just out of curiosity, have you considered using one of the drop-down
calendar controls to take your date input (the input mechanism is much more
natural for the user and it foolproof date-entry-wise)? I use the "Microsoft
Date and Time Picker Control" for this purpose. You can add it to your
UserForm's Toolbox by right-clicking the Toolbox and clicking on "Additional
Controls", then put a checkmark next to the "Microsoft Date and Time Picker
Control" item and click OK. Try it... I think you will like it.

Rick

'***********BEGIN PASTE***********
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
With TextBox1
If Not SecondTime Then
If .Text Like "*[!0-9/]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
LastPosition = TextBox1.SelStart
End Sub
'***********END PASTE***********