Results 1 to 5 of 5
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts

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

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

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    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?
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. #3
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    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]
    Me.MonthView1.Value = Me.MonthView1.MaxDate
    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

  4. The Following User Says Thank You to sdckapr For This Useful Post:

    zeddy (2013-09-15)

  5. #4
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    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
    Last edited by zeddy; 2013-09-15 at 16:02.

  6. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,815
    Thanks
    132
    Thanked 479 Times in 456 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •