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:
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
........
Display More
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:
And then:
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!