Date format in textbox

[Guest]

I have a userform with a textbox that I wish to enter dates in. Currently excel treats the datatype as text

1) What can I do to force it to enter dates instead of text
2) And to force any date validation

Regards

Bruce

 

[Bob Phillips]

Hi Bruce,

trap the values as entered

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Not IsDate(.Text) Then
MsgBox "Invalid date"
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
Cancel = True
End If
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 45 To 47 '/ - or .
Exit Sub
Case 48 To 57 'Nos 0 - 9
Exit Sub
Case Else
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
Beep
End Select

End Sub

You could make it tighter by ensuring that only 2 chars are ebtered before
a delimite, only two delimiters, same tyep, etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

I have a userform with a textbox that I wish to enter dates in. Currently

excel treats the datatype as text.