Hi,
I've had a search, but can't seem to find the right information !!
I have several macros that require users to input a start and end date to return a report based on these parametres. My question is how to validate the dates input so they can only enter within certain periods, and how to check that it is a proper date !!!
I have pieced together the following, but I know it's flawed :
The macro should finish and error message if :
If user enters nothing (or cancels)
If user enters text/non numerical
If user enters a date outside set period (not between 01/01/07 and 31/12/07)
If user enters a bizzare/non valid format i.e 001/decem/07
Otherwise the start and end date are recorded as AbsenceStart and AbsenceEnd to be used later on in the program.
It doesn't matter what format is entered 01/06/07 01-06-2007 01 June 2007 as long as excel understands that all of these formatts mean the same date.
Can anyone offer a suggestion ?
I've had a search, but can't seem to find the right information !!
I have several macros that require users to input a start and end date to return a report based on these parametres. My question is how to validate the dates input so they can only enter within certain periods, and how to check that it is a proper date !!!
I have pieced together the following, but I know it's flawed :
Code:
AbsenceStart = InputBox("Type month start date in format dd/mm/yy", "Start Date")
If AbsenceStart = "" Then MsgBox ("No Date Entered")
End If
If IsDate(AbsenceStart) Then
AbsenceEnd = InputBox("Type month end date in format dd/mm/yy", "End Date")
If AbsenceEnd = "" Then MsgBox ("No Date Entered")
End If
If IsDate(AbsenceEnd) Then
Resume Next
Else
MsgBox "You did not enter a valid date.", 48, "Invalid Date !"
End If
End If
The macro should finish and error message if :
If user enters nothing (or cancels)
If user enters text/non numerical
If user enters a date outside set period (not between 01/01/07 and 31/12/07)
If user enters a bizzare/non valid format i.e 001/decem/07
Otherwise the start and end date are recorded as AbsenceStart and AbsenceEnd to be used later on in the program.
It doesn't matter what format is entered 01/06/07 01-06-2007 01 June 2007 as long as excel understands that all of these formatts mean the same date.
Can anyone offer a suggestion ?