Using two calendar controls

[Patrickw]

I am desiging a userform with two calendar controls (Calendar Control
11.0). For illustration, lets assume the first calendar is October
and the second is November. The October calendar will be displayed to
the left with the current day selected (31) as a default. The second
(to the right of the first) will show the next month, November, with
no day picked. I have figured out how to make the two calendars react
to each other approriately, with one exception. When I pick one of
the greyed out dates at the beginning or end of the calendar (which
represent days in the previous and following months, respectively),
everything goes haywire. Any ideas on how to either keep the greyed
out days from showing at all or how to test for having picked them so
that I can write the proper handling routines? If one of the greyed
out dates at the end of the October calendar is picked, I would like
it to become November and the right hand calendar to become December.

 

[Doug Robbins - Word MVP]

Assuming that the calendar controls have the names of Calendar1 and
Calendar2, the following command will set the month of Calendar2 to the
month one after the month of the date selected in Calendar1, no matter
whether the data selected is one of the greyed out dates or not.

Private Sub Calendar1_Click()
Calendar2.Month = Calendar1.Month + 1
End Sub

For example if Calendar 1 was showing October and the greyed out date of the
30 before the 1 was clicked, the month of Calendar1 will change to September
and the month of Calendar2 will change to October.

If the greyed out date of 1 following 31 October is clicked, the month of
Calendar1 will change to November and the month of Calendar2 will change to
December.

--
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, originally posted via msnews.microsoft.com

 

[Patrickw]

Much, much, much simpler than what I was about to undertake. This is
the code I came up with:

Private Sub Calendar1_Click()
Calendar2.Day = 0
Calendar2.Month = Calendar1.Month + 1
Calendar1Title.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2Title.Caption = Format(Calendar2.Value, "MMMM yyyy")
End Sub

Private Sub Calendar2_Click()
Calendar1.Day = 0
Calendar1.Month = Calendar2.Month - 1
Calendar1Title.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2Title.Caption = Format(Calendar2.Value, "MMMM yyyy")
End Sub

Private Sub UserForm_Initialize()
Calendar1.Value = Date
Calendar1Title.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2.Value = Date
Calendar2.Month = Calendar1.Month + 1
Calendar2Title.Caption = Format(Calendar2.Value, "MMMM yyyy")
Calendar2.Day = 0
End Sub

Problem is, when I get to December in Calendar1, the caption won't
display in Calendar2. Likewise, when I get to January in Calendar2
when going back, December of the previous year will display in the
caption over Calendar1, but January won't display in Calendar2. Also,
when I select a date within the month on display (the blue days
instead of the grey days) in either Calendar1 or Calendar2 , it
nullifies the caption title over the opposing calendar.

 

[Graham Mayor]

What I suspect you need is

Private Sub Calendar1_Click()
Calendar2.Value = DateAdd("m", 1, Calendar1.Value)
UserForm1.Caption = Format(Calendar1.Value, "MMMM yyyy")
End Sub

Private Sub Calendar2_Click()
Calendar1.Value = DateAdd("m", -1, Calendar2.Value)
End Sub

Private Sub UserForm_Initialize()
Calendar1.Value = Date
UserForm1.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2.Value = DateAdd("m", 1, Calendar1.Value)
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Patrickw]

Thanks, Graham. It works perfectly, except for one little problem.
Let me first describe my userform layout:


LabelDisplayDateChosen

< Calendar1Title Calendar2Title >
_______________ _______________
|S|M|T|W|T|F|S| |S|M|T|W|T|F|S|
+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+
+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+
+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+
+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+
+-+-+-+-+-+-+-+ +-+-+-+-+-+-+-+

The grids are two separate calendar controls called Calendar1 and
Calendar2.
ShowDateSelectors and ShowTitle are set to false for both calendar
controls.
< is a label called LabelBack

is a label called LabelForward

Here is my code:

Private Sub Calendar1_Click()

LabelDisplayDateChosen.Caption = Format(Calendar1.Value, "dddd,
MMMM d, yyyy")
Calendar2.ValueIsNull = True

End Sub

Private Sub Calendar2_Click()

LabelDisplayDateChosen.Caption = Format(Calendar2.Value, "dddd,
MMMM d, yyyy")
Calendar1.ValueIsNull = True

End Sub

Private Sub LabelBack_Click()

Calendar1.Value = DateAdd("m", -2, Calendar2.Value)
Calendar2.Value = DateAdd("m", -1, Calendar2.Value)
Calendar1Title.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2Title.Caption = Format(Calendar2.Value, "MMMM yyyy")
Calendar1.ValueIsNull = True
Calendar2.ValueIsNull = False

End Sub

Private Sub LabelForward_Click()

Calendar1.Value = DateAdd("m", 1, Calendar1.Value)
Calendar2.Value = DateAdd("m", 1, Calendar1.Value)
Calendar1Title.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2Title.Caption = Format(Calendar2.Value, "MMMM yyyy")
Calendar1.ValueIsNull = False
Calendar2.ValueIsNull = True

End Sub

Private Sub UserForm_Initialize()

Calendar1.Value = Date
Calendar1Title.Caption = Format(Calendar1.Value, "MMMM yyyy")
Calendar2.Value = DateAdd("m", 1, Calendar1.Value)
Calendar2Title.Caption = Format(Calendar2.Value, "MMMM yyyy")
Calendar2.ValueIsNull = True

End Sub

Problem: When I click LabelBack followed by LabelForward, the year
resets to 1900.

 

[Graham Mayor]

Your code sets the values of Calendar1 and Calendar2 to Nul (ValueIsNull =
True) and then you are reading those nul values into the labels next time
you click the relevant button.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>

 

[Patrickw]

How can I keep both calendars from having a day selected? In other
words, I only want one calendar at a time to be able to have a day
selected. If I take out the ValueIsNull statement, a day is always
selected on both calendars, which is confusing.

 

[Graham Mayor]

Without going back to recheck (as I am about to go out) I would suggest that
you record the display information in a variable and update it when it
changes, then you don't have to read the nulled statement directly, but read
the variable instead.

I am not sure of the purpose of having two interconnected calendars. Unless
one is to set a start date and the other to set a variable end date you
would be better with just one calendar and perform any calculations in the
associated macro.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP


<>>< ><<> ><<> <>>< ><<> <>>< <>><<>