Setting the months displayed for a 3-month date picker (Excel2010)

Topic

New Reply

Hi

In the attached file, I have a date-picker.
When you click on the [show Calendar] button, a Form is displayed which shows a three-month calendar.

When the calendar is displayed, I would like it to show today’s date (if no previous date has been selected).
More importantly, I don’t want to see any ‘previous months’.
It currently shows Aug-Sep-Oct.

How can I set it to always display the current month as the first of the three months displayed?
(e.g. Sep-Oct-Nov for today’s date)

zeddy

Reply | Quote

Replies

Zeddy,

It seems that the Calendar control has gone missing in Office 2010! According to google results you can use the one for 2003 if properly registered but only in 32 bit Windows since it is a 32 bit control. So just what are you running (Office & Windows) and how did you get the control? :cheers:

May the Forces of good computing be with you!

RG

PowerShell & VBA Rule!
Computer Specs

Reply | Quote

If you add a line of code to change the date to the last month, when you select the desired date, you will have that month as the minimum.

Code:

Private Sub UserForm_Initialize()

zdate = [dateCell]
[COLOR="#FF0000"]Me.MonthView1.Value = Me.MonthView1.MaxDate[/COLOR]
If IsDate(zdate) Then
Me.MonthView1.Value = zdate
Else
Me.MonthView1.Value = Date
End If

End Sub

Note: if you only want 1 month displayed, you can change the monthcolumns from 3 to 1 (you can also change the Monthrows to a larger number to display more rows of months.

Steve

Reply | Quote

Hi Steve

Many thanks for that!
Thinking outside the box indeed!
Works brilliantly!

This is what I finally went with:

Code:

Private Sub UserForm_Initialize()                   'v2

Me.MonthView1.Value = Me.MonthView1.MaxDate  'initialise calendar to last poss. month
Me.MonthView1.Value = Date                   '..then show current month as first of 3

zDate = [interviewDateCell]                         'fetch value from named cell
If IsDate(zDate) Then                               'a date has been entered, so..
Me.MonthView1.Value = zDate                         '..show existing date in calendar
End If

End Sub

zeddy

Reply | Quote

Hi RG

Re post#2:
I have a few systems available, but mostly work on a core-i7, 64-bit Windows7 laptop.
On this particular laptop I have
Office2013 Professional,
Office2010 Professional,
Office2007 Professional,
Office2003 Professional.
..all working very nicely together.

By default, Microsoft Office installs the 32-bit version of Office even if your computer
is running 64-bit editions of Windows. There is a good reason for this.
Mainly, it’s because there aren’t many 64-bit versions of ActiveX controls available.
And existing 32-bit ActiveX controls don’t work with Office-64-bit.

The Calendar Control (called MSCAL.OCX) was last shipped with Office 2007.
(It is usually included with Access, so would be part of Office Professional, which includes Access)
It is not included in later versions of Office.

In Office 2010 they have replaced this date picker with an updated version in the
Active-X library, called MSCOMCT2.OCX. This is for 32-bit Office, NOT Office-64-bit.
(The MSCOMCT2.OCX is readily available for download from Microsoft)

1.Display the Developer tab of the ribbon.
2.In the [Controls] section, click the ‘Insert’ dropdown. Excel displays a palette of tools you can insert in your worksheet.
3.In the ActiveX Controls section of the palette (bottom), click the More Controls option. (It is the very bottom-right tool.)
Excel displays the More Controls scroll list.
4.Scroll through the alphabetic-sorted contols list..
Depending on your system, you might see..
Calendar Control 11.0
Microsoft Date and Time Picker Control 6.0 (SP6)
Microsoft MonthView Control 6.0 (SP6)

5.Click OK.

I used the MonthView control for my 3-month-view calendar.

zeddy

Reply | Quote