Hi Patrick,

"Patrick Simonds" wrote in message
...
You are right (based on the starting point I provided). I should have used
a better example. If you were to start with the second Tuesday of March on
Calendar1, when you got to Calendar3 you would be on the 1st Tuesday of
April and Calendar4 would be on the 3rd Tuesday of April.

It turns out that what I need to have happen, is that if I select the
2nd Tuesday (say of September) on Calendar1 then Calendar2 should have
the 4th Tuesday of September selected, Calendar3 the second Tuesday of
October and Calendar4 would display the 4th Tuesday of October. Of
course if I had started with the 4th Tuesday of September the I would
need the 2nd Tuesday of October and so on.


Try the following (lightly tested!):

'======================
Private Sub Calendar1_Click()
Dim YR As Long
Dim MTH As Long
Dim SecondTues As Double
Dim bValidDate

YR = Year(Calendar1)
MTH = month(Calendar1)

SecondTues = Evaluate("=DATE(" & YR & "," & MTH _
& ",15)-WEEKDAY(DATE(" & YR & "," & MTH & ",5))")

If Calendar1 < SecondTues And Calendar1 < SecondTues + 14 Then
'Neither 2nd nor 4th Tuesday selected!
MsgBox "The first calendar selection should be either the " _
& "SECOND or FOURTH TUESDAY in the selected month!"
'Reset each calendar to today's date!
Calendar1.Value = Date: Calendar2 = Date
Calendar3 = Date: Calendar4 = Date
Else
'Calendar1 selection is 2nd or 4th Tuesday!
bValidDate = True
End If

If bValidDate Then
If Day(Calendar1) <= 14 Then
'Second Tuesday selected
Calendar2 = Evaluate("=DATE(" & YR & "," & MTH _
& ",29)-WEEKDAY(DATE(" & YR & "," _
& MTH & ",5))")
Calendar3 = Evaluate("=DATE(" & YR & "," & MTH + 1 _
& ",15)-WEEKDAY(DATE(" & YR & "," & MTH + 1 & ",5))")
Calendar4 = Evaluate("=DATE(" & YR & "," & MTH + 1 _
& ",29)-WEEKDAY(DATE(" & YR & "," & MTH + 1 & ",5))")

Else
'Fourth Tuesday selected
Calendar2 = Evaluate("=DATE(" & YR & "," & MTH + 1 _
& ",15)-WEEKDAY(DATE(" & YR & "," & MTH + 1 & ",5))")
Calendar3 = Evaluate("=DATE(" & YR & "," & MTH + 1 _
& ",29)-WEEKDAY(DATE(" & YR & "," & MTH + 1 & ",5))")
Calendar4 = Evaluate("=DATE(" & YR & "," & MTH + 2 _
& ",15)-WEEKDAY(DATE(" & YR & "," & MTH + 2 & ",5))")
End If
End If

End Sub
'<<======================


---
Regards,
Norman



"Patrick Simonds" wrote in message
...
You are right (based on the starting point I provided). I should have used
a better example. If you were to start with the second Tuesday of March on
Calendar1, when you got to Calendar3 you would be on the 1st Tuesday of
April and Calendar4 would be on the 3rd Tuesday of April.






"Norman Jones" wrote in message
...
Hi Patrick,

It turns out that what I need to have happen, is that if I select the
2nd Tuesday (say of September) on Calendar1 then Calendar2 should have
the 4th Tuesday of September selected, Calendar3 the second Tuesday of
October and Calendar4 would display the 4th Tuesday of October. Of
course if I had started with the 4th Tuesday of September the I would
need the 2nd Tuesday of October and so on.

Your code responded in precisely the above manner for me.

What dates were displayed in your dependent calendar controls?

---
Regards,
Norman



"Patrick Simonds" wrote in message
...
I have a dialog box which contains 4 Calendars. The 4 calendars are set
up so that when you click on the 2nd or 4th Tuesday of the first calendar
the other calendars would progressively show the next Tuesday which was 2
weeks away, see code below.

It turns out that what I need to have happen, is that if I select the
2nd Tuesday (say of September) on Calendar1 then Calendar2 should have
the 4th Tuesday of September selected, Calendar3 the second Tuesday of
October and Calendar4 would display the 4th Tuesday of October. Of
course if I had started with the 4th Tuesday of September the I would
need the 2nd Tuesday of October and so on.

So it is obvious my code can not do this, so does anyone have an idea
how I might make this work?



Private Sub Calendar1_Click()

Calendar2 = Calendar1 + 14
Calendar3 = Calendar1 + 28
Calendar4 = Calendar1 + 42

End Sub