Hello. I have a long standing frustration when using input boxes to capture dates. I find that unless the date is entered in the long format (eg 25 Jul 2016) in the Input Box it doesn't display properly in the worksheet. Is there a work around? Ron
Hi Flipper,
You're right, there should be more options for inputbox validations.
You can put the inputbox in a loop, and exit the loop only when the input is a valid date. If the user will not type a valid date, he will be running in circles until they manage to type a date 🙂
The good part using this approach is that it will accept many date strings, like January 23, 2016, or 12/12/2016. You control the output by formatting the result of the input box, no matter what date format was typed.
Dim DateInput As Variant
Do
DateInput = InputBox("Date:", "Dates Only")
If IsDate(DateInput) Then Exit Do
If Not IsDate(DateInput) Then MsgBox "Try again, that was not a valid date", vbCritical + vbOKOnly, "Dates Only"
Loop
Sheet1.Cells(1).NumberFormat = "mm/dd/yyyy": Sheet1.Cells(1).Value = DateInput
End
Hi Ron,
You could use a date picker in your form so the date is always in a known format. No typing required for the user either.
Regards
Phil
Dates are pretty tough to handle given the different formats (US/UK formats) and selecting them from a list is the best as mentioned by Philip.
I normally allow users to enter the dates with some validation using a userform. Here is a simple userform to share with you that validate both dates and numbers.
Excellent, three very useful tips for handling dates thank you. I'm not sure which will suit me best right now so will give all three a try. The lack of control over the InputBox formatting is often frustrating, especially after designing a visually pleasing and bespoke user form.
Ron
