Textbox: Date Format and Validation

Good morning all,

I guess I'm handling parts of my data validation too rigidly. Currently, I enforce validation of dates using the following code:

Private Sub tbUHAEffectiveDate_AfterUpdate()
'Data Validation for Date Format
If Not tbUHAEffectiveDate.Value Like "##[/]##[/]####" Then
        MsgBox "Please enter in MM/DD/YYYY format"
        tbUHAEffectiveDate.SetFocus
End If
End Sub

I also default the box to show them the format in the Userform Initialize event:

tbUHAEffectiveDate.Value = "MM/DD/YYYY"

However, the users would like to be able to enter their date format in formats resembling a date and have it converted on the back end instead. For example, if the user enters "01012016", "1/1/2016", "1-1-16", etc., any of those should be acceptable and converted.

This information is being submitted to a table on a worksheet in this workbook as follows and that part doesn't matter much to me because the table can convert it there.

Dim Escalations As Worksheet
Set Escalations = ThisWorkbook.Sheets("Escalations")
nr = Escalations.Cells(Rows.Count, 1).End(xlUp).Row + 1


.....


Escalations.Cells(nr, 61) = tbUHAEffectiveDate


........

However, I am passing that information from some of the date fields to an .HTMLbody tag where I perform a calculation for the number of workdays using the following code:

Dim DateExpected As Date
   DateExpected = WorksheetFunction.WorkDay(Date, 2)

And then:

Format(DateExpected, "Long Date")

And I'm worried that a date in an improper format will result in an invalid operation performed.

Does anyone have a way to take entry resembling a date format and convert it to a date on the AfterUpdate event or in the Submit Click Event so that the conversion happens prior to the other actions?

What are your best practices when it comes to handling date fields?

As usual, any help is greatly appreciated!

Re: Textbox: Date Format and Validation

I guess no one has anything for this?

Re: Textbox: Date Format and Validation

Thanks for your response, cytop.

That code worked fine...I added it to the AfterUpdate event instead of the exit event as something seemed more preferential about that behavior. However, I'm trying to put the focus back on that textbox when there is an error and it's not working. Instead, it's going to the next input field in the Tab Order. Can you tell what I did wrong?

Private Sub tbOriginalSubmissionDate_AfterUpdate()
    If IsDate(tbOriginalSubmissionDate.Text) Then
        tbOriginalSubmissionDate.Text = Format(CDate(tbOriginalSubmissionDate.Text), "MM/DD/YYYY")
    Else
        Me.tbOriginalSubmissionDate.Value = ""
        tbOriginalSubmissionDate.BackColor = &HFFFF&
                MsgBox "Please enter a valid date."
                tbOriginalSubmissionDate.SetFocus
    End If
    If IsDate(tbOriginalSubmissionDate.Text) Then
        tbOriginalSubmissionDate.Text = Format(CDate(tbOriginalSubmissionDate.Text), "MM/DD/YYYY")
        tbOriginalSubmissionDate.BackColor = &H80000005
    End If
End Sub