Excel form does not recognize date entered

[Eprice]

Hi,
This is Excel 2000. I have a form where the user enters a next visit date. I am trying to stop the user from entering a date that has already passed. I have this as an error checker but it does not work, just lets the form unload and enters the past date.
stDate1 refers to the value entered in a text box on my form.
Dim stdt
stdt = FormatDateTime(stDate1, vbShortDate)
If stdt <= Now() Then
MsgBox "You cannot enter a date that has already passed. Please correct the Next Visit date."
Exit Sub
End If

Does anyone know why this is not working. Thanks,
Lisa

 

[dhookom]

I can't test in Excel 2000 but why not use validation where the value is >= =NOW()?

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Works for me:

Dim stdt As Date

If IsDate(stDate1.Text) Then
    stdt = FormatDateTime(stDate1, vbShortDate)
Else
    MsgBox "Not a valid Date."
    Exit Sub
End If

If stdt <= Date Then
    MsgBox "You cannot enter a date that has already passed. Please correct the Next Visit date."
Exit Sub

End If

Have fun.

---- Andy

 

[combo]

You have a date entered in a text box, that is stored as string. FormatDateTime converts date to string, you rather need the opposite. Convert string to date instead, use CDate function.

combo