Pop up calendar in userform - Need to limit entry

[Gordo24]

I have a userform with two pop up calendars (calendar1 and calendar2). Calendar1 is the "start date" and must be a Monday. I have this portion working fine. If they select a date other than a Monday an alert message will pop up notifying the user a Monday must be selected. I need to limit the user to a date selection in calendar2 to be a minimum of three years out from Calendar1. I would like an alert message to pop up in Calendar2 should the user select a date inside of my three year minimum. Am I even in the ballpark with the following:

If Trim(Calendar2.Value) < (Calendar1.Value + "not sure here") Then
Calendar2.SetFocus
MsgBox "Select End Date 3 Years Out From Start date", vbExclamation

Any ideas??

 

[AlphaFrog]

Try something like this...

If Calendar2.Value < DateAdd("yyyy", 3, Calendar1.Value) Then

 

[Gordo24]

Thanks AlphaFrog, at first pass it seems to work. In fact, looks like it works too well. The user typically will do a 3, 4 or 5 year "term." We run our "term" 3, 4, 5 (or longer) less one day. For example, 3 year term would be September 26, 2011 through September 25, 2014. Is there a way to use your code less one day?

 

[AlphaFrog]

If Calendar2.Value < DateAdd("yyyy", 3, Calendar1.Value - 1) Then

 

[Gordo24]

Thanks AlphaFrog, it worked perfectly. I was playing around with the -1 before I saw your last post, but I had it in the wrong spot. Thanks again. For others, for future reference, this code is in Sub cmdOK_Click()

If Calendar2.Value < DateAdd("yyyy", 3, Calendar1.Value - 1) Then
Calendar2.SetFocus
MsgBox "Select Minimum 3 Year term", vbExclamation
Exit Sub
End If

 

[sateesh kumar]

hi Mr.AlphaFrog

i am use your code, when i run the code am getting "Miss match error"
what i want expect is DOL Text-box accept the date if it's only Greater-than DOJ Dates.
here is code

Private Sub EmpDOL_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If EmpDOL.Value < DateAdd("yyyy", 3, EmpDOJ.Value - 1) Then
EmpDOL.SetFocus
MsgBox "enter correct date", vbExclamation
Exit Sub
End If

My Date Format is mm/dd/yyyy
am using Date picker.

Thanks for advance


thanks for advance