| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Calendar problem
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
Hi Patrick! You can also use the DateAdd function: Code: -------------------- Private Sub Calendar1_Click() Calendar2.Value = DateAdd("ww", 2, Calendar1.Value) Calendar3.Value = DateAdd("ww", 2, Calendar2.Value) Calendar4.Value = DateAdd("ww", 2, Calendar3.Value) 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 -- T-Žex ------------------------------------------------------------------------ T-Žex's Profile: http://www.excelforum.com/member.php...o&userid=26572 View this thread: http://www.excelforum.com/showthread...hreadid=466698 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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." |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
Thanks for the try, but that does not seem to work either.
"Patrick Molloy" wrote in message ... 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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" wrote in message ... 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Calendar problem
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" wrote in message ... Thanks for the try, but that does not seem to work either. "Patrick Molloy" wrote in message ... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| find free sharware to include calendar pop or use calendar in cell | Excel Discussion (Misc queries) | |||
| how do i export excel calendar info to outlook calendar? | Excel Discussion (Misc queries) | |||
| import calendar items from excel into outlook calendar | Excel Discussion (Misc queries) | |||
| Calendar Problem- What's Wrong? | Excel Discussion (Misc queries) | |||
| Calendar object in Excel problem - mscal.ocx | Excel Programming | |||
Linear Mode
