Assigning a date and doing error check using vba

I'm trying to get a user entered date assigned to a date variable EndDate. I would like to  do a couple of error checks:
1) If the date is not a date, e.g. someone enters text, then the user will be notified and sent back to the input box
2) If the date is before today, the user will be given an error message and sent back to the input box
 
Using the code below works fine for item 2 but if text is entered into the input box, an error occurs because EndDate type is Date

Solutions please...

Dim EndDate As Date

EnterDate: EndDate = InputBox("Enter end date for ADU and POs in mm/dd/yyyy format")

If EndDate < Date Then
    MsgBox "End date is earlier than or same as today. Enter valid end date."
   GoTo EnterDate
End If

Open in new window

EdLBAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Saurabh Singh TeotiaCommented:
You can do something like this to do what you are looking for..

Dim EndDate As Date
On Error Resume Next
EnterDate: EndDate = InputBox("Enter end date for ADU and POs in mm/dd/yyyy format")

If IsDate(ENDATE) = False Then
MsgBox "Please Enter a Valid Date"
   GoTo EnterDate
   End If

If EndDate < Date Then
    MsgBox "End date is earlier than or same as today. Enter valid end date."
   GoTo EnterDate
End If

Open in new window


Saurabh...
Neil RussellTechnical Development LeadCommented:
Would you not be better to pop up a form that has a DatePicker on it? you then know that it will ALWAYS be a valid date.
Bill PrewIT / Software Engineering ConsultantCommented:
Here is an example of one way to approach it:

Sub Test()

    Dim StringDate As String
    Dim EndDate As Date

EnterDate:     StringDate = InputBox("Enter end date for ADU and POs in mm/dd/yyyy format")

    ' See if CANCEL pressed
    If StringDate <> "" Then
    
        ' See if a valid date was entered
        If Not IsDate(StringDate) Then
            MsgBox "Invalid date entered."
            GoTo EnterDate
        End If

        ' Convert input string to date
        EndDate = DateValue(StringDate)
        
        ' Make sure it's after today
        If EndDate < Date Then
            MsgBox "End date is earlier than or same as today. Enter valid end date."
            GoTo EnterDate
        End If
    
    End If
    
End Sub

Open in new window

~bp

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
EdLBAuthor Commented:
Thanks, that worked perfectly.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.