VBA Input Box Date Validation

PARans

Board Regular
Joined
May 24, 2007
Messages
181
I am attempting to validate a date from an input box with the following code (which does not work and no doubt reveals my lack of vba experience)

Code:
dweekend = InputBox("Please enter week ending date (mm/dd/yy)", "WeekEnding")

If dweekend.Value < TODAY() - 30 Then GoTo error1
If dweekend.Value > TODAY() Then GoTo error2
End If

error1: MsgBox ("Date is older than 30 days")
error2: MsgBox ("Date is not valid")

The variable "dweekend" is set as a date. What I am trying to accomplish is for the user to have another opportunity to enter the date if what was entered is older than 30 days or greater than today but I don't know how to get the user back to the input box if this occurs. Otherwise the date would be used and the procedure would continue.

Excel 2003 / Windows XP
Thanks for your time
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
Try:

Code:
Sub Test()
    Dim dweekend As Date
    Dim Valid As Boolean
    Do While Valid = False
        dweekend = InputBox("Please enter week ending date (mm/dd/yy)", "WeekEnding")
        If dweekend < Date - 30 Then
            MsgBox "Date is older than 30 days"
        ElseIf dweekend > Date Then
            MsgBox "Date is in the future"
        Else
            Valid = True
        End If
    Loop
End Sub
 
Upvote 0
That works perfectly! Thank you so much for your speedy response and for providing such a simple solution. I had to run through your code a few times to catch on to the logic since I am still pretty green but I found it to be very straight forward. Just a couple quick questions if you would please:

How is it known at the start of this routine that Valid is false?
Is Date used in VBA like the Excel function TODAY?

Thank you
 
Upvote 0
That works perfectly! Thank you so much for your speedy response and for providing such a simple solution. I had to run through your code a few times to catch on to the logic since I am still pretty green but I found it to be very straight forward. Just a couple quick questions if you would please:

How is it known at the start of this routine that Valid is false?
Is Date used in VBA like the Excel function TODAY?

Thank you
 
Upvote 0
Since Valid is declared as Boolean it is False until explicitly set. And yes, Date is the VBA equivalent of TODAY().
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

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