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:


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


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


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





    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:


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


    And then:


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


    There's a basic conflict in your post...


    Quote

    the users would like to be able to enter their date format in formats resembling a date

    but you're testing for a specific format in the Validate event. The 2 are not compatible.


    Try a simple routine in the Exit event



    The only thing that can't handle is input like 20160410, but it can handle 4/10, 4 10, 16 4 10, 10 Apr or even the standard 4/10/16 (All dates will be resolved using your regional settings)

  • 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?


  • Re: Textbox: Date Format and Validation


    You can cancel the Exit event, but not the AfterUpdate event.


    In any case, it is not a good idea to force a user back to a control. That sort of validation is best carried out when the OK/Submit or whatever button is clicked.