Results 1 to 6 of 6

VBA Input Box Date Validation

This is a discussion on VBA Input Box Date Validation within the Excel Questions forums, part of the Question Forums category; I am attempting to validate a date from an input box with the following code (which does not work and ...

  1. #1
    Board Regular
    Join Date
    May 2007
    Posts
    181

    Default VBA Input Box Date Validation

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,460

    Default

    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

  3. #3
    Board Regular
    Join Date
    May 2007
    Posts
    181

    Default

    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

  4. #4
    Board Regular
    Join Date
    May 2007
    Posts
    181

    Default

    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

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    69,460

    Default

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

  6. #6
    Board Regular
    Join Date
    May 2007
    Posts
    181

    Default

    This board is fabuous! Thank you!

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com