VBA Input Box Date Validation

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: VBA Input Box Date Validation

  1. #1
    Board Regular
    Join Date
    May 2007
    Posts
    181
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    This board is fabuous! Thank you!

User Tag List

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