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

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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).

Reply | Quote

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…..

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

This code checks whether a date is OK:

Run the sub below named test.

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)  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

Reply | Quote

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?

Reply | Quote

Ooops, I just forgot to remove it.

Reply | Quote