Calendar problem

Patrick Simonds · 09-11-2005, 11:05 PM

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

Norman Jones · 09-12-2005, 12:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
>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
>

Patrick Simonds · 09-12-2005, 12:05 AM

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

**T-®ex** · 09-12-2005, 12:19 AM

Hi Patrick! You can also use the DateAdd function:

Please Login or Register  to view this content.

Originally Posted by Patrick Simonds

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

Norman Jones · 09-12-2005, 03:05 AM

Hi Patrick,

"Patrick Simonds" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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" <[email protected]> wrote in message
>> news:[email protected]...
>>>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
>>>
>>
>>
>
>

Patrick Molloy · 09-12-2005, 03:05 AM

Private Sub Calendar1_Click()
Dim date1 As Date
Dim date2 As Date
date1 = DateSerial(Calendar1.Year, Calendar1.Month, 1)
Do Until Weekday(date1, vbMonday) = 2
date1 = date1 + 1
Loop
date1 = date1 + 7 'set 2nd Tuesdaye
date2 = date1 + 14 '4th tuesday

Select Case Calendar1.Value
Case date1
Calendar2.Value = Calendar1.Value + 14
Calendar3.Value = Calendar1 + 28
Calendar4.Value = Calendar1.Value + 42
Case date2
Calendar2.Value = date1 + 28
Calendar3.Value = Calendar1 + 28
Calendar4.Value = Calendar2 + 28
Case Else

End Select

End Sub

"Patrick Simonds" wrote:

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

Patrick Simonds · 09-12-2005, 03:05 AM

All that seems to do is move me ahead two weeks. That is not what I need.

"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.

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

Patrick Simonds · 09-12-2005, 04:05 AM

Thanks for the try, but that does not seem to work either.

"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
> Private Sub Calendar1_Click()
> Dim date1 As Date
> Dim date2 As Date
> date1 = DateSerial(Calendar1.Year, Calendar1.Month, 1)
> Do Until Weekday(date1, vbMonday) = 2
> date1 = date1 + 1
> Loop
> date1 = date1 + 7 'set 2nd Tuesdaye
> date2 = date1 + 14 '4th tuesday
>
> Select Case Calendar1.Value
> Case date1
> Calendar2.Value = Calendar1.Value + 14
> Calendar3.Value = Calendar1 + 28
> Calendar4.Value = Calendar1.Value + 42
> Case date2
> Calendar2.Value = date1 + 28
> Calendar3.Value = Calendar1 + 28
> Calendar4.Value = Calendar2 + 28
> Case Else
>
> End Select
>
>
> End Sub
>
> "Patrick Simonds" wrote:
>
>> 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
>>
>>
>>

Patrick Simonds · 09-12-2005, 04:05 AM

If you try this staring with the second week of March (2005) you will see
that in April it is the first and third Tuesday selected.

"Patrick Molloy" <[email protected]> wrote in message
news:[email protected]...
> Private Sub Calendar1_Click()
> Dim date1 As Date
> Dim date2 As Date
> date1 = DateSerial(Calendar1.Year, Calendar1.Month, 1)
> Do Until Weekday(date1, vbMonday) = 2
> date1 = date1 + 1
> Loop
> date1 = date1 + 7 'set 2nd Tuesdaye
> date2 = date1 + 14 '4th tuesday
>
> Select Case Calendar1.Value
> Case date1
> Calendar2.Value = Calendar1.Value + 14
> Calendar3.Value = Calendar1 + 28
> Calendar4.Value = Calendar1.Value + 42
> Case date2
> Calendar2.Value = date1 + 28
> Calendar3.Value = Calendar1 + 28
> Calendar4.Value = Calendar2 + 28
> Case Else
>
> End Select
>
>
> End Sub
>
> "Patrick Simonds" wrote:
>
>> 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
>>
>>
>>

Patrick Simonds · 09-12-2005, 04:05 AM

If you try this staring with the second week of March (2005) you will see
that in April it is the first and third Tuesday selected.

"Patrick Simonds" <[email protected]> wrote in message
news:[email protected]...
> Thanks for the try, but that does not seem to work either.
>
> "Patrick Molloy" <[email protected]> wrote in
> message news:[email protected]...
>> Private Sub Calendar1_Click()
>> Dim date1 As Date
>> Dim date2 As Date
>> date1 = DateSerial(Calendar1.Year, Calendar1.Month, 1)
>> Do Until Weekday(date1, vbMonday) = 2
>> date1 = date1 + 1
>> Loop
>> date1 = date1 + 7 'set 2nd Tuesdaye
>> date2 = date1 + 14 '4th tuesday
>>
>> Select Case Calendar1.Value
>> Case date1
>> Calendar2.Value = Calendar1.Value + 14
>> Calendar3.Value = Calendar1 + 28
>> Calendar4.Value = Calendar1.Value + 42
>> Case date2
>> Calendar2.Value = date1 + 28
>> Calendar3.Value = Calendar1 + 28
>> Calendar4.Value = Calendar2 + 28
>> Case Else
>>
>> End Select
>>
>>
>> End Sub
>>
>> "Patrick Simonds" wrote:
>>
>>> 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
>>>
>>>
>>>
>
>