Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    I've got a form with some textboxes, among these are a
    textbox containing future dates. The user need to input
    this date.

    I want to assure that a uniform date format is used.
    (dd/mm/yyyy)

    I'm currently using the "Change" event on the textbox and
    trying to use the format(date, "dd/mm/yyyy), but for some
    reason I get an error with this.

    Does anyone have a suggestion to how I solve to validate
    this ??


    Thanks in advance and
    best regards,
    Henrik
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    The Change event is fired at every keystroke. So you're trying to format the result after he/she has just typed the first character. Try using the BeforeUpdate or AfterUpdate event; these fire when the user has finished editing. With BeforeUpdate you can check the input and cancel it if you don't like it by setting the Cancel parameter to True.

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    In addition to what Hans said, there are a couple of other possibilities:

    Your message said you were trying to use format(date, "dd/mm/yyyy) to format the date.

    1- If that is the exact statement, there is a missing double quote. It should be format(date, "dd/mm/yyyy").

    2- What is date in that statement? Date is an Excel VBA function which returns the current system date. Therefore, your format statement will format the current system date, not the date in the textbox.

    3- If date in your statement is supposed to represent the value from the textbox, that value is text, not a date value and format will not be able to format it. You would need to use format(DateValue(txtDate), "dd/mm/yyyy).

    4- Where is this textbox? Is it on a UserForm or on a worksheet (your message seems to indicate it is on a userform, but that is not 100% clear). If it is on a worksheet, is it the textbox from the Controls toolbox or from the forms toolbox? If the textbox is on a UserForm, then you probably want to use the Exit event rather than the Change event for the reasons that Hans gave. If it is on a worksheet, then we need to know which textbox you used.
    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    Thanks for your answers so far.
    Does the snippet below clarify ?
    - Textbox is called "txtEventDate"
    - currently this one refuses to go around the "if...format(chkDate,"dd/mm/yy)" - it enters the errorhandler every time no matter the format.

    Any suggestions for improvent ? (Bet you have/ ;o)))

    Thanks,
    ;o) Henrik
    ************************************************** ********************************************

    Private Sub txtEventDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Dim chkDate

    On Error GoTo EventDate_ErrorHandler

    If Cancel = True Then Exit Sub

    chkDate = txtEventDate.Text
    If Not Format(chkDate, "dd/mm/yy") Then
    MsgBox "Incorrect date format - Please try again" & Chr(13) & Chr(13) & "Must be dd/mm/yy", vbOKOnly, "NDS - Add event"
    Cancel = True
    End If

    txtEventDate.SetFocus

    Exit Sub

    EventDate_ErrorHandler:
    If Err.Number = 13 Then Resume Next

    End Sub
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    The statement

    If Not Format(chkDate, "dd/mm/yy") Then

    will never work - Format returns a string, and Not expects to be followed by a boolean. Format isn't meant to check input, you use it to format output.

    You can let users enter a date any way they want, and let VBA test if it's valid:

    If Not IsDate(txtEventDate.Text) Then

    or you can put 3 separate, clearly labeled text boxes txtDay, txtMonth and txtYear on your form, and assemble the date from these with the DateSerial function.

    Alternatively, you might use a calendar control on your form. See Using a Calendar Control in Excel. (Thanks to sdckapr for providing this link in <post#=204606>post 204606</post#>).

  6. #6
    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: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    Just as an additional point, IsDate can produce strange results, presumably due to the fact that it tries every conceivable permutation of what is passed to it - this is particularly true if you pass it a 2-digit year. Furthermore, something like IsDate("4a1-2-3") evaluates to True whilst IsDate("4d1-2-3") evaluates to False. I'm sure there's a logic there somewhere..... <img src=/S/confused.gif border=0 alt=confused width=15 height=20>
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    Using strings to input dates is rather messy in international situations - if you're mixing local settings and VBA and SQL, you have to be very careful.

    As another illustration of your point: if you enter 12-19-2002 on a system with dd-mm-yyyy date setting, Windows reasons that 19 isn't a valid month number, so it is interpreted as 19-12-2002.

    About your example:

    4a1-2-3 is interpreted as 4 AM on 1-2-3 (which is valid in any date format)
    4p1-2-3 is interpreted as 4 PM on 1-2-3
    Any other letter is invalid.

  8. #8
    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: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    Thanks, Hans. For some reason I had it in my head that "3b1-2-3" had produced true too. It's been a long day!
    It may also be worth mentioning that IsDate will recognise some dates that Excel won't, so again, it's not always the best check for this sort of situation.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    Hi Jan Karel,

    What is the purpose of the While ... Wend construction? It seems to cause an endless loop if the user enters an invalid date. Isn't that an overly harsh punishment? <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    Ooops, I just forgot to remove it.

    <Off to edit the post!!!>
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Validate date from textbox (Wni 2000/ Excel 2000 sp2)

    *****EDITED, REMOVED WHILE_WEND construct in the Code.

    This code checks whether a date is OK:

    Run the sub below named test.

    <pre>Option Explicit

    Function IsDateValid(sdate As String) As Boolean
    Dim bDateOK As Boolean
    Dim sDay As String
    Dim sMonth As String
    Dim sYear As String
    Dim iDaysInMonth As Integer
    Dim iTemp As Integer
    If sdate = "" Then Exit Function
    If sdate Like "##-##-####" Or _
    sdate Like "#-##-####" Or _
    sdate Like "#-#-####" Or _
    sdate Like "##-#-####" Then
    sDay = Left(sdate, InStr(sdate, "-") - 1)
    sMonth = Mid(sdate, Len(sDay) + 2, 2)
    If Right(sMonth, 1) = "-" Then sMonth = Left(sMonth, 1)
    sYear = Right(sdate, 4)
    iDaysInMonth = Choose(Val(sMonth), 31, 28, 31, 30, 31, 30, 31, 31, 30, 31, 30, 31)
    If Val(sMonth) > 0 And Val(sMonth) < 13 Then
    If sMonth = "2" Or sMonth = "02" Then
    iTemp = Val(sYear)
    If (iTemp Mod 100) = 0 Then
    If iTemp Mod 400 = 0 Then
    iDaysInMonth = 29
    End If
    ElseIf (iTemp Mod 4) = 0 Then
    iDaysInMonth = 29
    End If
    End If
    If Val(sDay) < 1 Or Val(sDay) > iDaysInMonth Then
    bDateOK = False
    Else
    bDateOK = True
    End If
    Else
    bDateOK = False
    End If
    End If
    IsDateValid = bDateOK
    End Function

    Sub test()
    Dim sdate As String
    sdate = InputBox("Please enter a valid date! (dd-mm-yyyy)")
    If sdate = "" Then Exit Sub
    MsgBox IsDateValid(sdate)
    End Sub


    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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