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?

Here’s how it looks (bottom Textbox)

TBDate1

TBDate2

3 thoughts on “Entering Dates In Textboxes

  1. 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.

  2. 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

  3. 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.


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.