MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

User Form Date Validation

  Asked By: Sebastion    Date: Mar 01    Category: MS Office    Views: 1193

I need to validate a date entry from a userform control:

Should this validation be in the beforeupdate or afterupdate event.

I need to validate that the entry is a date entry.
That is not a date in the future of todays date
and that the date entry is not older than date - 100.

The field/txtbox must not be left empty.

My initial attempt just does not seem to work as expected, see below.
Private Sub txtDateRcvd_BeforeUpdate(ByVal Cancel As
Dim mvValue As String
Dim mvdate As Date
mvValue = txtDateRcvd.Value
If IsDate(mvValue) Then
mvdate = Format(CDate(mvValue), "dd/mm/yyyy")
Select Case mvdate
Case mvdate > Date
MsgBox ("Invalid date, cannot be received in the
txtDateRcvd.Value = ""
Cancel = True
Case mvdate < Date - 100
MsgBox ("Invalid Date, received date in excess of
100 days")
txtDateRcvd.Value = ""
Cancel = True
Case Else
txtDateRcvd.Value = mvValue
End Select
MsgBox ("Invalid date format, please use 'dd/mm/yyyy'")
txtDateRcvd.Value = ""
Cancel = True
End If
End Sub



6 Answers Found

Answer #1    Answered By: Atid Boonliang     Answered On: Mar 01

I threw together this which seemed to work.

Private Sub txtDateReceived_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If ((Myform.txtDateReceived.Value) <> "") Then
If (Not IsDate(Myform.txtDateReceived.Value)) Then
MsgBox "NOt a proper date"
Cancel = True
If (DateDiff("D", Myform.txtDateReceived.Value, Now) < 0) Then
MsgBox "Date may not be a future  date"
Cancel = True
ElseIf (DateDiff("D", Myform.txtDateReceived.Value, Now) > 100) Then
msg = "Date may not be more than 100 days in the past"
msg = msg & Chr(13) & Myform.txtDateReceived & " is "
msg = msg & DateDiff("D", Myform.txtDateReceived.Value, Now) & "
MsgBox msg
Cancel = True
Cancel = False
End If
End If
Cancel = False
End If
End Sub

Answer #2    Answered By: Hayden Evans     Answered On: Mar 01

I have just got home & have a granddaughters
20th birthday party to go to this evening, so will try your
suggestion out tomorrow. I will update you in due course.

Answer #3    Answered By: Sairah Hashmi     Answered On: Mar 01

Tried & tested and works exactly as required.

I replaced the line:
Myform.txtDateReceived.Value = ""
so as to clear the textbox when focus returns to it.

Very appreciative of your time and expertise.

Answer #4    Answered By: Rosa Reynolds     Answered On: Mar 01

No problem.
I just "assumed" that you wanted to allow the user  to fix the minor error in the
date field rather than re-entering it.
I've had users complain that they entered it RIGHT, but my CODE read it wrong!

This way, they can SEE that they wrote it wrong and I don't have to listen to
them whine!!!

Answer #5    Answered By: Geldefsman Bakker     Answered On: Mar 01

Yes, this is a very valid point. Also, of course, the likelihood is that
they only typed one thing wrong, so having to re-enter the whole date
instead of just fixing the one character, will not be appreciated.

Answer #6    Answered By: Von Fischer     Answered On: Mar 01

I personally prefer to include the user  entry within the error message
and clear the user entry  on return to the control.
Hopefully encouraging the user to be more accurate in the future.
But having said that I do appreciate your points of view, comments and

Didn't find what you were looking for? Find more on User Form Date Validation Or get search suggestion and latest updates.