Entering Dates In Textboxes
The accounting software that I use (Timberline) has a nice feature in that you can enter a six-digit number in a date field and it will convert it automatically. I wanted the same functionality in my userform Textboxes, so I came up with this Exit event procedure. Does anyone have a better way?
‘Test the entry in the application date textbox for errors
If IsDate(Me.TxtAppDate.Value) Then ‘If a valid date was entered
‘Reformat Date
Me.TxtAppDate.Value = Format(Me.TxtAppDate.Value, “mm / dd / yy”)
‘If a number of length 6 was entered
ElseIf Len(Me.TxtAppDate.Value) = 6 Then
‘Turn it into a date
Me.TxtAppDate.Value = DateSerial(Right(Me.TxtAppDate.Value, 2), _
Left(Me.TxtAppDate.Value, 2), Mid(Me.TxtAppDate.Value, 3, 2))
Else
MsgBox “You must enter a valid date.”
Cancel = True
End If
End Sub
Here’s how it looks (bottom Textbox)
This would be my (somewhat obvious) solution. I’m not sure if it’s better, exactly.
ElseIf Len(Me.TxtAppDate.Value) = 6 And IsNumeric(Me.TxtAppDate.Value) Then
Me.TxtAppDate.Value = Left(Me.TxtAppDate.Value, 2) & “/” & Mid(Me.TxtAppDate.Value, 3, 2) & “/” & Right(Me.TxtAppDate.Value, 2)
You could shorten the code significantly by using With/End With.
With/End With:
Private Sub TxtAppDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TxtAppDate
If IsDate(.Value) Or (.TextLength = 6 And IsNumeric(.Value)) Then _
.Value = Format(.Value, IIf(IsDate(.Value), “Short Date”, “00/00/00?)) Else _
If .TextLength > 0 Then Cancel = CBool(MsgBox(“You must enter a valid date.”) = vbOK)
End With
End Sub
Almost all my apps use my DateTxt (dates) and a NumTxt (numbers) textbox classes. Since I live in Europe (dd.mm format and ISO weeknumbering) the code itself is too long and irrelevant to paste here, but accepted date formats are:
all standard date entries, plus
1204 (no year = this year, as in standard date entry)
120404 (years 0-30 = 2000 to 2030, 31-99 = 1931 – 1999)
12042004
W12 (first day, optional last day, of week 12 this year)
W 12 03 (first day, optional last day, of week 12 2003)
W1203 (same as above)
W 12 2003 (same as above)
There’s no magic or beauty invloved, code like this bloats pretty quick. But seeing that the result works for all kinds of users makes it worth the extra effort.