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]

In that case, I can just change the variable 'mdy' to 'dmy' and specify the output format as "dd/mm/yyyy" instead of "mm/dd/yyyy".

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

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

 

[robertvdb]

Thanks John ! Much appreciated.