Logo 
Search:

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: 1057
  

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

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
MSForms.ReturnBoolean)
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
future")
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
Else
MsgBox ("Invalid date format, please use 'dd/mm/yyyy'")
txtDateRcvd.Value = ""
Cancel = True
End If
End Sub

Share: 

 

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"
Myform.txtDateReceived.SetFocus
Cancel = True
Else
If (DateDiff("D", Myform.txtDateReceived.Value, Now) < 0) Then
MsgBox "Date may not be a future  date"
Myform.txtDateReceived.SetFocus
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) & "
ago"
MsgBox msg
Myform.txtDateReceived.SetFocus
Cancel = True
Else
Cancel = False
End If
End If
Else
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.SetFocus
with
Myform.txtDateReceived.SetFocus
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
assistance

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




Tagged:        

 
 
 
 

Related Post