Hey guys. Hows it going? I'm thinking there is a simple solution to my problem, but for some reason I can't think of it.
I have a userform that the user enters data and when they click submit the data that was entered is put into the appropriate worksheet. Simple. Well, I have an archive code that moves all data entered at the end of the day into the appropriate archive file based on the date and time of the data entered. Still with me? The problem I'm having is that every once in a while we have "User error" where a user goes to enter the appropriate time into the textbox, and inadvertently hits 2 keys at the same time, or just hits the wrong key, and then submits the data before checking for mistakes. Well, then when they try to archive the data, excel doesn't know where to put the data because the date and time aren't in the correct format because of the mistake. So, I want to check the textbox and make sure it is in the correct format upon clicking submit.
Here's the Code I've got right now.
arrivaltime is the TextBox that has the date/time
AT_Error is just a label that becomes visible reminding the user to enter the date and time in the correct format.
This code works if the date/time in the TB is missing a number, but if it's got extra numbers it still submits the data. For instance: if the data in the TB is "03/16/2017 20:556", the time is obviously wrong, but it still submits it. But if the data is "03/16/2017 20:5", the code works, shows the error message and ends the sub correctly.
Thanks for any help you guys can give me.
I have a userform that the user enters data and when they click submit the data that was entered is put into the appropriate worksheet. Simple. Well, I have an archive code that moves all data entered at the end of the day into the appropriate archive file based on the date and time of the data entered. Still with me? The problem I'm having is that every once in a while we have "User error" where a user goes to enter the appropriate time into the textbox, and inadvertently hits 2 keys at the same time, or just hits the wrong key, and then submits the data before checking for mistakes. Well, then when they try to archive the data, excel doesn't know where to put the data because the date and time aren't in the correct format because of the mistake. So, I want to check the textbox and make sure it is in the correct format upon clicking submit.
Here's the Code I've got right now.
Code:
If Not arrivaltime = Format(arrivaltime.Value, "mm/dd/yyyy hh:mm") Then
AT_Error.Visible = True
Exit Sub
End If
arrivaltime is the TextBox that has the date/time
AT_Error is just a label that becomes visible reminding the user to enter the date and time in the correct format.
This code works if the date/time in the TB is missing a number, but if it's got extra numbers it still submits the data. For instance: if the data in the TB is "03/16/2017 20:556", the time is obviously wrong, but it still submits it. But if the data is "03/16/2017 20:5", the code works, shows the error message and ends the sub correctly.
Thanks for any help you guys can give me.