Checking Date/Time formatting upon submitting data in userform

Peter h

Active Member
Joined
Dec 8, 2015
Messages
417
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.
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.
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Why not add
Code:
 If Not ... And Len(arrivaltime.Value)>16 Then

I actually did not know about the Len function (still learning vba). That should work perfect though. Thank you very much. I'll test it later when I get to work, but it looks like that should give me the exact result in looking for.

Quick question (I only ask because I'm not able to get on and test it right now), does the formatting check for valid dates/times, or does it only count the number of characters entered to see if it matches the formatting? For instance, if someone types in 03/32/2017... does it see that as the right formatting, or will it trigger my if statement because it's an invalid date?
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,034
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top