Set Focus and Incorrect Dates

[Ann]

I'm having a problem with getting SetFocus to work and with 1/1/2008 being
recognized as later then 12/31/2007. I have listed the code below. (Sorry
this is so long).

It originally had a Cancel = True on the Event but I was asked to remove it.
The reason being if the txtBenefitPeriodEndDate is before the txt
BenefitPeriodBeginDate but it's the begin date that was entered incorrectly
then the user has to enter a date in the txtBenefitPeriodEndDate before it
will allow them to get back to the txtBenefitPeriodBeginDate.

The two sets of dates I have work together. They are:

txtBenefitPeriodStartDate
txtBenefitPeriodEndDate (must be later then txtBenefitPeriodStartDate)
txtHRAStartDate (must be later or equal to txtBenefitPeriodStartDate)
txtHRAEndDate (must be later then txtHRAStartDate and equal to the
txtBenefitPeriodEndDate)

Since they do work together I just want to remove the text from
txtBenefitEndDate and keep the cursor there because if it moves to
txtHRAStartDate then I get that message if I click into
txtBenefitPeriodStartDate.

Here is the code for txtBenefitPeriodEndDate and txtHRAStartDate:

Private Sub txtBenefitPeriodEndDate_Exit(ByVal Cancel As
MSForms.ReturnBoolean)

' 5.) Benefit Period End Date must be greater then 4.)
'Benefit Period Start Date.

If txtBenefitPeriodEndDate.Value < txtBenefitPeriodStartDate.Value Then

MsgBox "The Benefit Period End Date must be" _
& vbCrLf & "after the Benefit Period Start Date."
txtBenefitPeriodEndDate = Null
txtBenefitPeriodEndDate.SetFocus
End If

End Sub


Private Sub txtHRAStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

' 6.) HRA Start Date must be later or equal to 4.) Benefit Period Start Date

If txtHRAStartDate.Value < txtBenefitPeriodStartDate Then

MsgBox "The HRA Start Date must be later or" _
& vbCrLf & "equal to the Benefit Period Start Date"
txtHRAStartDate.Value = Null
txtHRAStartDate.SetFocus
End If

End Sub

I am just beginning to learn and am very confused why SetFocus doesn't seem
to work. It seems to work fine if I want it to go in a forward motion but if
I try to get it to stay where it is or go backwards it just doesn't work.

Also, the dates. It works if 01/01/2008 (txtBenefitPeriodEndDate) is before
01/02/2008 (txtBenefitPeriodStartDate) but not if 12/31/2007 is prior to
01/01/2008. Any help I can get is greatly appreciated. Thanks.

 

[fumei via OfficeKB.com]

The details are a bit overwhelming, and not totally relevant. The issue is
you are using the _Exit event.

Think about this.

The Exit event fires just before it actually exits. In other words, the
instructions in the Exit event are actioned.....and then it exits!

So....

Private Sub txtHRAStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)

do this
do that

MAKE MYSELF THE FOCUS
txtHRAStartDate.SetFocus

End Sub



....and then Exit (myself)

 

[Doug Robbins - Word MVP]

You should use the CDate() function to ensure that you are really making a
data comparison

If CDate(txtEnd.Value) - CDate(txtStart.Value) <= 0 Then


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

 

[Gordon Bentley-Mix]

Ann,

You were on the right track with the Cancel argument, but you were just
missing a couple of details on what you should evaluate when setting it to
True. I reckon what you're looking for is probably something like this:

Option Explicit

Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False
'*** See if TextBox1 is empty
If Len(TextBox1.Value) > 0 Then
'*** If TextBox1 isn't empty, see it the value that's in it is a date
If IsDate(TextBox1.Value) Then
'*** If the value in TextBox1 is a date, see if TextBox2 is empty
If Len(TextBox2.Value) > 0 Then
'*** If TextBox2 isn't empty, see if the value that's in it
is a date
If IsDate(TextBox2.Value) Then
'*** If they're both dates then see if date in TextBox1
'*** is greater than the date in TextBox2
If CDate(TextBox1.Value) > CDate(TextBox2.Value) Then
'*** If the dates aren't OK, warn & retain focus
MsgBox "The date in TextBox1 must be before the date
in TextBox2.", vbCritical, "Date Error"
TextBox1.Value = ""
Cancel = True
Exit Sub
End If
Else
'*** If the value in TextBox2 isn't a date, warn & set
focus to TextBox2
'(altho for all practical purposes you should never be
able to get here)
MsgBox "The value in TextBox2 is not a date.",
vbCritical, "Date Error"
With TextBox2
.Value = ""
.SetFocus
End With
Exit Sub
End If
End If
Else
'*** If the value in TextBox1 isn't a date, warn & retain focus
MsgBox "The value in TextBox1 is not a date.", vbCritical, "Date
Error"
TextBox1.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End Sub

Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False
'*** See if TextBox2 is empty
If Len(TextBox2.Value) > 0 Then
'*** If TextBox2 isn't empty, see it the value that's in it is a date
If IsDate(TextBox2.Value) Then
'*** If the value in TextBox2 is a date, see if TextBox1 is empty
If Len(TextBox1.Value) > 0 Then
'*** If TextBox1 isn't empty, see if the value that's in it
is a date
If IsDate(TextBox1.Value) Then
'*** If they're both dates then see if date in TextBox1
'*** is greater than the date in TextBox2
If CDate(TextBox1.Value) > CDate(TextBox2.Value) Then
'*** If the dates aren't OK, warn & retain focus
MsgBox "The date in TextBox2 must be after the date
in TextBox1.", vbCritical, "Date Error"
TextBox2.Value = ""
Cancel = True
Exit Sub
End If
Else
'*** If the value in TextBox1 isn't a date, warn & set
focus to TextBox1
'(altho for all practical purposes you should never be
able to get here)
MsgBox "The value in TextBox1 is not a date.",
vbCritical, "Date Error"
With TextBox1
.Value = ""
.SetFocus
End With
Exit Sub
End If
End If
Else
'*** If the value in TextBox2 isn't a date, warn & retain focus
MsgBox "The value in TextBox2 is not a date.", vbCritical, "Date
Error"
TextBox2.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End Sub

Private Sub FormatDates()
If Len(TextBox1.Value) > 0 Then
TextBox1.Value = Format(TextBox1.Value, "dd mmm yyyy")
End If
If Len(TextBox2.Value) > 0 Then
TextBox2.Value = Format(TextBox2.Value, "dd mmm yyyy")
End If
End Sub

By clearing the value from the TextBox when the validation fails, you'll be
able to set focus to the other TextBox if necessary (because the code allows
the TextBox to be empty).

You'll need to substitute the names of your TextBoxes for TextBox1 and
TextBox2 and extend the concept to work with the business logic around your
four dates, but this should get you there. Just be careful of the line breaks
if/when you copy the code. (And watch your use of Null; you should be setting
the .Value property of the control to an empty string: "".)
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Ann]

OMG! Gordon, that was so nice of you. I used this for all four dates and it
works beautifully. Thanks so much for taking the time...and you commented it
so well I didn't have a single problem getting it to work and to understand
it. I've learned so much from you...thank you. I had been feeling very
overwhelmed.

 

[Gordon Bentley-Mix]

Ann,

I’m please you found my code useful. However, after I posted it I re-read
your original post and realised that the business logic you were trying to
support was somewhat more complicated than I first thought. Consequently, I
decided to have another look at the it, as I believe the complexity of the
logic may prove problematic - especially when each field must be validated
against three others.

Here is my understanding of your requirements:
* Benefit Period Start Date must be before Benefit Period End Date and HRA
End Date, and before or equal to HRA Start Date;
* Benefit Period End Date must be after Benefit Period Start Date and HRA
Start Date, and before or equal to HRA End Date;
* HRA Start Date must be after or equal to Benefit Period Start Date, and
before HRA End Date and Benefit Period End Date; and
* HRA End Date must be after HRA Start Date and Benefit Period Start Date,
and after or equal to Benefit Period End Date.

I developed the following code (sans comments) to support this business
logic on the ferry ride home last night and refined and tested it on the ride
in this morning:

Option Explicit

Private Sub txtBenefitPeriodStartDate_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Cancel = False
If Me.Visible = True Then
If Len(txtBenefitPeriodStartDate.Value) > 0 Then
If IsDate(txtBenefitPeriodStartDate.Value) Then
If Len(txtBenefitPeriodEndDate.Value) > 0 Then
If IsDate(txtBenefitPeriodEndDate.Value) Then
If CDate(txtBenefitPeriodStartDate.Value) >=
CDate(txtBenefitPeriodEndDate.Value) Then
MsgBox "The Benefit Period Start Date must be
before the Benefit Period End Date.", vbCritical, "Date Error"
txtBenefitPeriodStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAEndDate.Value) > 0 Then
If IsDate(txtHRAEndDate.Value) Then
If CDate(txtBenefitPeriodStartDate.Value) >=
CDate(txtHRAEndDate.Value) Then
MsgBox "The Benefit Period Start Date must be
before the HRA End Date.", vbCritical, "Date Error"
txtBenefitPeriodStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAStartDate.Value) > 0 Then
If IsDate(txtHRAStartDate.Value) Then
If CDate(txtBenefitPeriodStartDate.Value) >
CDate(txtHRAStartDate.Value) Then
MsgBox "The Benefit Period Start Date must be on
or before the HRA Start Date.", vbCritical, "Date Error"
txtBenefitPeriodStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
Else
MsgBox "The value entered for the Benefit Start Date is not
a valid date.", vbCritical, "Date Error"
txtBenefitPeriodStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End If
End Sub

Private Sub txtBenefitPeriodEndDate_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Cancel = False
If Me.Visible = True Then
If Len(txtBenefitPeriodEndDate.Value) > 0 Then
If IsDate(txtBenefitPeriodEndDate.Value) Then
If Len(txtBenefitPeriodStartDate.Value) > 0 Then
If IsDate(txtBenefitPeriodStartDate.Value) Then
If CDate(txtBenefitPeriodEndDate.Value) <=
CDate(txtBenefitPeriodStartDate.Value) Then
MsgBox "The Benefit Period End Date must be
after the Benefit Period Start Date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAStartDate.Value) > 0 Then
If IsDate(txtHRAStartDate.Value) Then
If CDate(txtBenefitPeriodEndDate.Value) <=
CDate(txtHRAStartDate.Value) Then
MsgBox "The Benefit Period End Date must be
after the HRA Start Date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAEndDate.Value) > 0 Then
If IsDate(txtHRAEndDate.Value) Then
If CDate(txtBenefitPeriodEndDate.Value) >
CDate(txtHRAEndDate.Value) Then
MsgBox "The Benefit Period End Date must be on
or before the HRA End Date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
Else
MsgBox "The value entered for the Benefit End Date is not a
valid date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End If
End Sub

Private Sub txtHRAStartDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False
If Me.Visible = True Then
If Len(txtHRAStartDate.Value) > 0 Then
If IsDate(txtHRAStartDate.Value) Then
If Len(txtBenefitPeriodStartDate.Value) > 0 Then
If IsDate(txtBenefitPeriodStartDate.Value) Then
If CDate(txtHRAStartDate.Value) <
CDate(Me.txtBenefitPeriodStartDate.Value) Then
MsgBox "The HRA Start Date must be on or after
the Benefit Period Start Date.", vbCritical, "Date Error"
txtHRAStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtBenefitPeriodEndDate.Value) > 0 Then
If IsDate(txtBenefitPeriodEndDate.Value) Then
If CDate(txtHRAStartDate.Value) >=
CDate(txtBenefitPeriodEndDate.Value) Then
MsgBox "The HRA Start Date must be before the
Benefit Period End Date.", vbCritical, "Date Error"
txtHRAStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAEndDate.Value) > 0 Then
If IsDate(txtHRAEndDate.Value) Then
If CDate(txtHRAStartDate.Value) >=
CDate(txtHRAEndDate.Value) Then
MsgBox "The HRA Start Date must be before the
HRA End Date.", vbCritical, "Date Error"
txtHRAStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
Else
MsgBox "The value entered for the HRA Start Date is not a
valid date.", vbCritical, "Date Error"
txtHRAStartDate.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End If
End Sub

Private Sub txtHRAEndDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False
If Me.Visible = True Then
If Len(txtHRAEndDate.Value) > 0 Then
If IsDate(txtHRAEndDate.Value) Then
If Len(txtHRAStartDate.Value) > 0 Then
If IsDate(txtHRAStartDate.Value) Then
If CDate(txtHRAEndDate.Value) <=
CDate(txtHRAStartDate.Value) Then
MsgBox "The HRA End Date must be after the HRA
Start Date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtBenefitPeriodStartDate.Value) Then
If IsDate(txtBenefitPeriodStartDate.Value) Then
If CDate(txtHRAEndDate.Value) <=
CDate(txtBenefitPeriodStartDate.Value) Then
MsgBox "The HRA End Date must be after the
Benefit Period Start Date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtBenefitPeriodEndDate.Value) Then
If IsDate(txtBenefitPeriodEndDate.Value) Then
If CDate(txtHRAEndDate.Value) >
CDate(txtBenefitPeriodEndDate.Value) Then
MsgBox "The HRA End Date must be on or after the
Benefit Period End Date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
Else
MsgBox "The value entered for the HRA End Date is not a
valid date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End If
End Sub

Private Sub FormatDates()
If Me.Visible = True Then
If Len(txtBenefitPeriodStartDate.Value) > 0 Then
txtBenefitPeriodStartDate.Value =
Format(txtBenefitPeriodStartDate.Value, "dd MMMM, yyyy")
End If
If Len(txtBenefitPeriodEndDate.Value) > 0 Then
txtBenefitPeriodEndDate.Value =
Format(txtBenefitPeriodEndDate.Value, "dd MMMM, yyyy")
End If
If Len(txtHRAStartDate.Value) > 0 Then
txtHRAStartDate.Value = Format(txtHRAStartDate.Value, "dd MMMM,
yyyy")
End If
If Len(txtHRAEndDate.Value) > 0 Then
txtHRAEndDate.Value = Format(txtHRAEndDate.Value, "dd MMMM, yyyy")
End If
End If
End Sub

I’ve tested the above and believe it works in all cases, provided my
understanding of your requirements is correct.

Again, be careful of the line breaks if you copy and paste from the
newsgroup. Alternatively, I can email you my template to use as a source.

Finally, a quick explanation on the use of Me.Visible = True throughout:
this is simply to ensure that the code is not triggered if the user cancels
the UserForm (assuming that there is a 'Cancel' button and the first thing
that the code behind it does is hide the UserForm).

Let me know if you have any questions.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Gordon Bentley-Mix]

Ann,

I just noticed that my FormatDates procedure was formatting the values to NZ
style (almost) rather than American - and then not to a format that's even
close to what's in your original post.

To rectify this problem, change all instances of "dd MMMM, yyyy" to
"m/d/yyyy".

(Eight years of living in NZ and I've all but forgotten the "American" way
of life... <g>)
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.

 

[Ann]

WOW! That's a lot to look over. I'll have to really study it. I did go
over your four bullets and they were two that I changed. They should read:

* Benefit Period End Date must be after Benefit Period Start Date and HRA

Start Date, and after (not before) or equal to HRA End Date;

* HRA End Date must be after HRA Start Date and Benefit Period Start Date,

and before (not after) or equal to Benefit Period End Date.

The HRA Start Date and HRA End Date can be the same as the Benefit Period
Start Date and End Date or it has to fall between those two dates.

Also, there is a Cancel button that unloads the form.

I did notice the format for the date after I tested it and was able to
change them..thanks.

 

[Gordon Bentley-Mix]

* Benefit Period End Date must be after Benefit Period Start Date and HRA

* HRA End Date must be after HRA Start Date and Benefit Period Start Date,

The HRA Start Date and HRA End Date can be the same as the Benefit Period
Start Date and End Date or it has to fall between those two dates.

Ann,

The changes required to accommodate the revised validation are as follows
(look for '<*** HERE ***):

Private Sub txtBenefitPeriodEndDate_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
Cancel = False
If Me.Visible = True Then
If Len(txtBenefitPeriodEndDate.Value) > 0 Then
If IsDate(txtBenefitPeriodEndDate.Value) Then
If Len(txtBenefitPeriodStartDate.Value) > 0 Then
If IsDate(txtBenefitPeriodStartDate.Value) Then
If CDate(txtBenefitPeriodEndDate.Value) <=
CDate(txtBenefitPeriodStartDate.Value) Then
MsgBox "The Benefit Period End Date must be
after the Benefit Period Start Date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAStartDate.Value) > 0 Then
If IsDate(txtHRAStartDate.Value) Then
If CDate(txtBenefitPeriodEndDate.Value) <=
CDate(txtHRAStartDate.Value) Then
MsgBox "The Benefit Period End Date must be
after the HRA Start Date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtHRAEndDate.Value) < 0 Then
If IsDate(txtHRAEndDate.Value) Then
If CDate(txtBenefitPeriodEndDate.Value) >
CDate(txtHRAEndDate.Value) Then '<*** HERE ***
MsgBox "The Benefit Period End Date must be on
or before the HRA End Date.", vbCritical, "Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
Else
MsgBox "The Benefit End Date is not a date.", vbCritical,
"Date Error"
txtBenefitPeriodEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End If
End Sub

Private Sub txtHRAEndDate_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = False
If Me.Visible = True Then
If Len(txtHRAEndDate.Value) > 0 Then
If IsDate(txtHRAEndDate.Value) Then
If Len(txtHRAStartDate.Value) > 0 Then
If IsDate(txtHRAStartDate.Value) Then
If CDate(txtHRAEndDate.Value) <=
CDate(txtHRAStartDate.Value) Then
MsgBox "The HRA End Date must be after the HRA
Start Date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtBenefitPeriodStartDate.Value) Then
If IsDate(txtBenefitPeriodStartDate.Value) Then
If CDate(txtHRAEndDate.Value) <=
CDate(txtBenefitPeriodStartDate.Value) Then
MsgBox "The HRA End Date must be after the
Benefit Period Start Date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
If Len(txtBenefitPeriodEndDate.Value) Then
If IsDate(txtBenefitPeriodEndDate.Value) Then
If CDate(txtHRAEndDate.Value) <
CDate(txtBenefitPeriodEndDate.Value) Then '<*** HERE ***
MsgBox "The HRA End Date must be on or after the
Benefit Period End Date.", vbCritical, "Date Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
End If
Else
MsgBox "The HRA End Date is not a date.", vbCritical, "Date
Error"
txtHRAEndDate.Value = ""
Cancel = True
Exit Sub
End If
End If
FormatDates
End If
End Sub

I'm pretty sure this is right. Let me know if you'd like me to send you my
"dev" template.
--
Cheers!
Gordon

Uninvited email contact will be marked as SPAM and ignored. Please post all
follow-ups to the newsgroup.