VBA autocomplete date in userform

[robertvdb]

I have an Excel userform in which are Textboxes where various dates have to be entered.

I am looking for VBA which will autocomplete dates that are partially entered:

- If I put 02/10 instead of 02/10/2022, I'd like the year to be added automatically
- Or if I put 2/10/2022 I'd like the first 0 to be put automatically.
- Or if I put 021022 then the result will be 02/10/2022.

Thanks for any help.

 

[John_w]

Call this function from the appropriate event handler in your userform. It returns the string argument passed to it formatted as a mm/dd/yyyy string or displays an error message.

Public Function Format_Date(inputDate As String) As String

    Dim parts As Variant
    parts = Split(inputDate, "/")
    Select Case UBound(parts)
        Case Is = 0
            If Len(inputDate) = 6 Then
                Format_Date = Format(DateSerial(Mid(inputDate, 5, 2), Mid(inputDate, 1, 2), Mid(inputDate, 3, 2)), "mm/dd/yyyy")
            Else
                Format_Date = ""
                MsgBox "Error: " & inputDate & " unknown date format", vbExclamation
            End If
        Case Is = 1
            Format_Date = Format(DateSerial(Year(Date), parts(0), parts(1)), "mm/dd/yyyy")
        Case Is = 2
            Format_Date = Format(DateSerial(parts(2), parts(0), parts(1)), "mm/dd/yyyy")
        Case Else
            Format_Date = ""
            MsgBox "Error: " & inputDate & " unknown date format", vbExclamation
    End Select
    
End Function

 

[robertvdb]

Thanks John.

So I have to call this Function when I exit the date-textbox on the userform ?

 

[John_w]

Yes, that's one way:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim formattedDate  As String
    formattedDate = Format_Date(TextBox1.Text)
    If formattedDate <> "" Then
        TextBox1.Text = formattedDate
    Else
        Cancel = True
    End If
End Sub

Note the function doesn't validate the input and would return an incorrect date or VBA error if the user enters something that it doesn't recognise as a date.

 

[robertvdb]

Thanks John,

very useful and working !

All I still need is a way to avoid typing the slashes. So for today's date, I would enter 02162022 or 021622, and the slashes would be added automatically upon exiting the text boxes...

 

[John_w]

So for today's date, I would enter 02162022 or 021622

Try this improved date validation and formatting function.

Public Function Format_Date(inputDate As String, outputDate As String) As Boolean

    Dim parts As Variant
    Dim mdy As String
    
    Format_Date = True 'assume valid input date
    parts = Split(inputDate, "/")
    Select Case UBound(parts)
        Case Is = 0
            If Len(inputDate) = 6 Then
                mdy = Mid(inputDate, 1, 2) & "/" & Mid(inputDate, 3, 2) & "/" & Mid(inputDate, 5, 2)
                If IsDate(mdy) Then
                    outputDate = Format(CDate(mdy), "mm/dd/yyyy")
                Else
                    outputDate = "Error: '" & inputDate & "' invalid date"
                    Format_Date = False
                End If
            ElseIf Len(inputDate) = 8 Then
                mdy = Mid(inputDate, 1, 2) & "/" & Mid(inputDate, 3, 2) & "/" & Mid(inputDate, 5, 4)
                If IsDate(mdy) Then
                    outputDate = Format(CDate(mdy), "mm/dd/yyyy")
                Else
                    outputDate = "Error: '" & inputDate & "' invalid date"
                    Format_Date = False
                End If
            Else
                outputDate = "Error: '" & inputDate & "' unknown date format"
                Format_Date = False
            End If
        Case Is = 1
            mdy = parts(0) & "/" & parts(1) & "/" & Year(Date)
            If IsDate(mdy) Then
                outputDate = Format(CDate(mdy), "mm/dd/yyyy")
            Else
                outputDate = "Error: '" & inputDate & "' invalid date"
                Format_Date = False
            End If
        Case Is = 2
            mdy = parts(0) & "/" & parts(1) & "/" & parts(2)
            If IsDate(mdy) Then
                outputDate = Format(CDate(mdy), "mm/dd/yyyy")
            Else
                outputDate = "Error: '" & inputDate & "' invalid date"
                Format_Date = False
            End If
        Case Else
            outputDate = "Error: '" & inputDate & "' unknown date format"
            Format_Date = False
    End Select
    
End Function

Call it from an event handler in your userform like this:

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim formattedDate As String, validDate As Boolean
    validDate = Format_Date(TextBox1.Text, formattedDate)
    If validDate Then
        TextBox1.Text = formattedDate
    Else
        MsgBox formattedDate
        Cancel = True
    End If
End Sub

 

[robertvdb]

Thanks John, works wonderfully !

The only small issue I still have, is a small code adaption for the European date format dd:mm/yyyy

 

[robertvdb]

Thanks John, works wonderfully !

The only small issue I still have, is a small code adaption for the European date format dd/mm/yyyy

 

[John_w]

Do you want it handle both US date and European dates, with the input in the user's locale, whether that is mm/dd/yyyy or dd/mm/yyyy, or the input formats previously described, and output in the user's locale either mm/dd/yyyy for US or dd/mm/yyyy for European dates? It's not that simple to handle both date formats.

 

[robertvdb]

No, all I need right now is your code being adapted to handle European dates.