Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validating Dates In Userforms

    Below is code that I use in a Word 97 template
    that allows me to make sure I correctly enter the desired date in a date
    format in the inputbox. This works beautifully. But, I would like to also
    be able to have a routine that does the same thing if I enter a date in a
    textbox in a userform. However, I cannot figure out how to adapt this
    routine to do that. Your help would be greatly appreciated in this. Thank
    you. Here is the code used when entering dates into inputboxes.

    Dim Response As Long

    Do
    StartDate = InputBox("Enter Starting Date")
    If IsDate(StartDate) Then

    Response = MsgBox("Entered date: " & _
    Format(StartDate, "mmmm d, yyyy") & _
    vbCr & vbCr & "Is this correct?", _
    vbYesNo + vbQuestion)

    If Response = vbYes Then Exit Do
    Else
    MsgBox "Invalid Date, please re-enter."
    End If
    Loop

    Gary

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Validating Dates In Userforms

    Hi Gary,
    I'd put pretty much the same code into the BeforeUpdate event of the textbox on your userform except change InputBox(...) to Me.textboxname and instead of using a loop, you can just set Cancel to true and it will reject the date in the textbox.
    Does that help?
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Dates In Userforms

    Rory,

    Thanks for the reply. I am not familiar with the BeforeUpdate event you mentioned. I wanted to simply put that in code behind a commanbutton that when pressed enters the date if valid and some other information to be filled in. If the date is not valid, then the message box would appear and when clicked bring focus back to userform. Or, if date is valid, would bring up the Yes/No msgbox to make sure that is the date you wanted to insert. If yes is pressed procedure would go forward. If No is pressed, the focus would shift back to the userform for the date to be changed.

    Have not really used the statement you entioned about Cancel. Could you elaborate a bit more please?

    Thanks again.

    Gary

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Validating Dates In Userforms

    Gary,
    You can do it with a command button if you prefer - it doesn't make a lot of difference!
    all you'd need is something like:
    If isdate(me.textboxname) then
    response = ....
    Else
    MsgBox "Not a valid date!"
    me.textboxname = ""
    me.textboxname.setfocus
    end if
    and then any other code you need.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Dates In Userforms

    Thanks for the clarification, Rory. Will do some experimenting with this and see if I can get it to work properly. Thanks again.

    Gary

  6. #6
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Validating Dates In Userforms

    I'll just add a thought. Your example seems to require that StartDate actually be a valid date when you format it. If you are concerned that it might NOT be a valid date, I'm guessing the result from Format would be gibberish. To "pre-treat" the string believed to contain a date I am partial to the CDate() function, which will scrub sloppily entered dates into a reasonable form. For example, it will, during the year 2001, convert 5/1 to 5/1/2001 (with the meaning set in your international settings control panel). Truly good programming practice would dictate testing the input with IsDate(me.textbox) first, but I'm not *that* anal.

  7. #7
    Star Lounger
    Join Date
    Jan 2001
    Posts
    65
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validating Dates In Userforms

    thanks for your input. Will consider this. I do use CDate in other templates for various purposes.

    Gary

Posting Permissions

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