none
Excel - Date & Time Picker / Calendar Controls - multi compatible ???

    Question

  • Work: Windows 7 & MS Office Prof(?) 2007
    Home: Windows Vista & MS Office 2010 Trial
    Other employees: Windows XP to 7 & MS Office 2003 to 2010

    I have a question about 'Microsoft Date and Time Picker' & 'Calendar' controls between different versions of Excel. I want to use a datetime picker (pref a calendar control though) in a spreadsheet & have it compatible across multiple versions (mainly 2007 & 2010 for now) without the user (any employee in a large company) having to install a file/add-in.

    Calendar Control 12.0 = Excel 2007
    Microsoft Date and Time Picker 6.0 (SP6) = Excel 2010

    Any ideas please what the difference is between the two?

    Spreadsheets I've created at work will NOT run at home due to Calendar Control having been removed from 2010 (grrrr why?).
    <
    • Edited by shell_l_d Monday, June 28, 2010 12:37 AM
    Saturday, June 26, 2010 2:31 AM

Answers

  • I've come up with this code instead... it will advise by msgbox if mscomct2.ocx is not installed or not registered or if trust centre option is turned off so unable to check. Hopefully someone else can use it. :)

    VBAProject - Module 2:

    [code]
    Sub GetVbReferences()
    
      ' ASSUMPTION: a worksheet called "VbReferences" exists.
      ' Write to worksheet code per samanco in http://www.mrexcel.com/forum/showthread.php?t=442346
      ' Added my own code to check if mscomct2.ocx is installed &/or registered or if trust centre option enabled.
      ' If not, a msgbox will display with full instructions on what to do/check.
      
      On Error Resume Next
      
      Dim n As Integer
      Dim s1 As String, s2 As String, s3 As String
          
      'GUID & Name of "Microsoft Windows Common Controls-2"
      s2 = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}"
      s3 = "mscomct2.ocx"
      
      ' Fill VbReferences worksheet with details on selected VBProject References
      With ThisWorkbook.Worksheets("VbReferences")
        .Cells.Clear
        .Cells(1, 1) = "Item"
        .Cells(1, 2) = "Name"
        .Cells(1, 3) = "Type"
        .Cells(1, 4) = "Description"
        .Cells(1, 5) = "Is Broken"
        .Cells(1, 6) = "Major"
        .Cells(1, 7) = "Minor"
        .Cells(1, 8) = "GUID"
        .Cells(1, 9) = "Full Path"
        .Cells(1, 10) = "Built In"
      
        n = ActiveWorkbook.VBProject.References.Count 'Compile error: Invalid use of property
        If n = 0 Then
          MsgBox "Unable to check if " & s3 & " (Microsoft Windows Common Controls-2) is installed &/or registered." _
            & "This is required for the date selection objects to be usable." _
            & vbCrLf & vbCrLf & "Check if this is enabled:" _
            & vbCrLf & vbCrLf & "Excel 2003 & XP:" _
            & vbCrLf & " * Tools > Macro > Security > Trusted Sources >" _
            & vbCrLf & "  tick 'Trust access to Visual Basic Project' check box > OK." _
            & vbCrLf & vbCrLf & "Excel 2007 & 2010:" _
            & vbCrLf & " * Click Microsoft Office button > Excel Options >" _
            & vbCrLf & "  Trust Center > Trust Center Settings > Macro Settings >" _
            & vbCrLf & "  tick 'Trust access to the VBA project object model' check box > OK." _
            , vbInformation, s3 & " installed or registered?"
          Exit Sub
        End If
                    
        ' loop through all VBProject References
        For n = 1 To ActiveWorkbook.VBProject.References.Count
          
          Select Case ActiveWorkbook.VBProject.References.Item(n).Type
            Case 0: s1 = "TypeLib"
            Case 1: s1 = "Project"
          End Select
          
          'ERROR NOTE: Name, Description & FullPath can cause: Method 'Name' of object 'Reference' failed
          .Cells(n + 1, 1) = n
          .Cells(n + 1, 2) = ActiveWorkbook.VBProject.References.Item(n).Name
          .Cells(n + 1, 3) = s1
          .Cells(n + 1, 4) = ActiveWorkbook.VBProject.References.Item(n).Description
          .Cells(n + 1, 5) = ActiveWorkbook.VBProject.References.Item(n).IsBroken
          .Cells(n + 1, 6) = ActiveWorkbook.VBProject.References.Item(n).Major
          .Cells(n + 1, 7) = ActiveWorkbook.VBProject.References.Item(n).Minor
          .Cells(n + 1, 8) = ActiveWorkbook.VBProject.References.Item(n).GUID
          .Cells(n + 1, 9) = ActiveWorkbook.VBProject.References.Item(n).FullPath
          .Cells(n + 1, 10) = ActiveWorkbook.VBProject.References.Item(n).BuiltIn
          
          ' Check if mscomct2.ocx is installed &/or registered so DTPicker recognised
          ' (Microsoft Windows Common Controls-2)
          If ActiveWorkbook.VBProject.References.Item(n).GUID = s2 Then
            ' NOT installed
            If ActiveWorkbook.VBProject.References.Item(n).IsBroken = True Then
              MsgBox s3 & " (Microsoft Windows Common Controls-2)" _
                & vbCrLf & "is not installed (or registered). " _
                & "This is required for the date selection objects to be usable." _
                & vbCrLf & vbCrLf & "1) Download mscomct2.cab from http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB" _
                & vbCrLf & "2) Unzip/extract mscomct2.cab (.ocx & .inf)" _
                & vbCrLf & "3) Copy extracted files to relevant directory as administrator (click continue if prompted for admin permission)" _
                & vbCrLf & "  c:\windows\system\  = Windows 95, 98, or ME" _
                & vbCrLf & "  c:\WINNT\system32\  = Windows NT or 2000" _
                & vbCrLf & "  c:\windows\system32\ = Windows XP or 7" _
                & vbCrLf & "  c:\windows\sysWOW64\ = Windows 7 64bit" _
                & vbCrLf & "4) Close & re-open spreadsheet." _
                , vbCritical, s3 & " missing!"
            ' registered
            ElseIf InStr(0, ActiveWorkbook.VBProject.References.Item(n).FullPath, s3) > 0 Then
              'do nothing
            ' NOT registered
            Else
              MsgBox s3 & "(Microsoft Windows Common Controls-2)" _
                & vbCrLf & "is not registered. " _
                & "This is required for the date selection objects to be usable." _
                & vbCrLf & vbCrLf & "Register it by doing ONE of the following &" _
                & vbCrLf & "close & re-open spreadsheet." _
                & vbCrLf & " * Start > Run > regsvr32 [fullpath]/mscomct2.ocx" _
                & vbCrLf & " * Start > Programs > Accessories >" _
                & vbCrLf & "  right click 'Command Prompt' >" _
                & vbCrLf & "  Run as administrator > regsvr32 [fullpath]/mscomct2.ocx" _
                & vbCrLf & vbCrLf & "Full paths:" _
                & vbCrLf & "  regsvr32 c:\windows\system\  = Windows 95, 98, or ME" _
                & vbCrLf & "  regsvr32 c:\WINNT\system32\  = Windows NT or 2000" _
                & vbCrLf & "  regsvr32 c:\windows\system32\ = Windows XP or 7" _
                & vbCrLf & "  regsvr32 c:\windows\sysWOW64\ = Windows 7 64bit" _
                , vbCritical, s3 & " not registered!"
            End If
          End If
          
        Next n
        .Cells.EntireColumn.AutoFit
        
      End With
      
      Exit Sub
      
    End Sub
    [/code]

    • Marked as answer by Bessie Zhao Thursday, July 01, 2010 8:11 AM
    Monday, June 28, 2010 4:25 PM

All replies

  • So... I have several spreadsheets created using Excel 2007 with 'Calendar Control 12.0' controls in them. It wont work in Excel 2010 as that control was removed from Office 2010!!!

    Now... I also have a different spreadsheet created in Excel 2010 with 'Microsoft Date and Time Picker 6.0 (SP6)' controls in it. It wont work in Excel 2007 as that control doesn't exist in Office 2007!!!

    I want to have a multi-compatible spreadsheet (that at least works with Excel 2007 & 2010 & probably also 2003) with a date/time/calendar control in it but dont really want to have to instruct each person who may use it to install some add-in to their Excel.

    What options do I have please?

    Monday, June 28, 2010 12:02 AM
  • I assume this is related...

    The spreadsheet created with Excel 2010 has DTPicker controls in it, when I open it in Excel 2007 it gives this popup msgbox:

    Calendar Control 12.0 in Excel 2007

    Microsoft Visual Basic
    Compile error:
    Can't find project or library

    Highlights 'Format' formula in this extract of the macro:

      ' Obtain the start & end dates
      dteStartDate = Format(Worksheets("Update").DTPickerStart, "yyyy-mm-dd")
      dteEndDate = Format(Worksheets("Update").DTPickerEnd, "yyyy-mm-dd")

    I just checked VBA > Tools > References for Excel 2007 & it has these selected by default (on my machine):

    • Visual Basic For Applications
    • Microsoft Excel 12.0 Object Library
    • OLE Automation
    • Microsoft Office 12.0 Object Library
    • Microsoft ActiveX Data Objects 6.0 Library
    • MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)
    • Microsoft Forms 2.0 Object Library

    When I click on 'MISSING: Microsoft Windows Common Controls-2 6.0 (SP6)' it shows location:  C:\Windows\system32\MSCOMCT2.OCX

    Monday, June 28, 2010 12:30 AM
  • Furthermore... there appears to be a BUG/ISSUE with DTPicker which has apparently been around since at least 2004... is there a solution/fix to this please? As per the details in this thread: http://www.mrexcel.com/forum/showthread.php?t=70813&page=2
    Monday, June 28, 2010 12:35 AM
  • I've copied the Calendar Control 12.0 file(s) to a 2010 machine without any problems. 

    The CC 12.0 and Date & Time picker work differently.  One is a like a form the other is like a Combo box.  I was able to get the D&T to work on user forms in 2007 and 2010, but could not get it to work in the spreadsheet in 2007.  Probably something simple I'm missing.


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
    Monday, June 28, 2010 1:58 AM
  • I suspect you can copy the file for the D&T picker as I did for the Calendar Control.

     


    If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire
    Monday, June 28, 2010 2:01 AM
  • Yes I'm aware of this, thanks Shane. I was hoping there was a way to do it such that someone could open it in Excel 2003/2007/2010 without having to install any missing files. Maybe an automated way?

    The controls are both being used on the spreadsheets.

    The calendar control is a calendar to select dates

    Date & Time Picker is a combo box whereby each item (day or month or year ...) can be changed using up & down arrow keys (or typed in) or when you click on the field it pops up a calendar. :)

    Monday, June 28, 2010 2:05 AM
  • Other than having to give a user these type of instructions (assuming they're correct):

    How to install 'Microsoft Date & Time Picker' if it's missing:

    * Download MSCOMCT2.OCX   http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB

    * Save downloaded file in relevant directory:

     For Windows 95, 98, or ME:
     c:\windows\system\

     For Windows NT or 2000:
     c:\WINNT\system32\

     For Windows XP or 7:
     c:\windows\system32\

    * Register the library file: 
     eg:  Start > Run > regsvr32 \windows\system32\MSCOMCT2.OCX

     For Windows 95, 98, or ME:
     regsvr32 \windows\system\MSCOMCT2.OCX

     For Windows NT or 2000:
     regsvr32 \WINNT\system32\MSCOMCT2.OCX

     For Windows XP or 7:
     regsvr32 \windows\system32\MSCOMCT2.OCX

      You should see a message saying "DllRegisterServer ... succeeded" 

    Monday, June 28, 2010 2:39 AM
  • I've come up with this code instead... it will advise by msgbox if mscomct2.ocx is not installed or not registered or if trust centre option is turned off so unable to check. Hopefully someone else can use it. :)

    VBAProject - Module 2:

    [code]
    Sub GetVbReferences()
    
      ' ASSUMPTION: a worksheet called "VbReferences" exists.
      ' Write to worksheet code per samanco in http://www.mrexcel.com/forum/showthread.php?t=442346
      ' Added my own code to check if mscomct2.ocx is installed &/or registered or if trust centre option enabled.
      ' If not, a msgbox will display with full instructions on what to do/check.
      
      On Error Resume Next
      
      Dim n As Integer
      Dim s1 As String, s2 As String, s3 As String
          
      'GUID & Name of "Microsoft Windows Common Controls-2"
      s2 = "{86CF1D34-0C5F-11D2-A9FC-0000F8754DA1}"
      s3 = "mscomct2.ocx"
      
      ' Fill VbReferences worksheet with details on selected VBProject References
      With ThisWorkbook.Worksheets("VbReferences")
        .Cells.Clear
        .Cells(1, 1) = "Item"
        .Cells(1, 2) = "Name"
        .Cells(1, 3) = "Type"
        .Cells(1, 4) = "Description"
        .Cells(1, 5) = "Is Broken"
        .Cells(1, 6) = "Major"
        .Cells(1, 7) = "Minor"
        .Cells(1, 8) = "GUID"
        .Cells(1, 9) = "Full Path"
        .Cells(1, 10) = "Built In"
      
        n = ActiveWorkbook.VBProject.References.Count 'Compile error: Invalid use of property
        If n = 0 Then
          MsgBox "Unable to check if " & s3 & " (Microsoft Windows Common Controls-2) is installed &/or registered." _
            & "This is required for the date selection objects to be usable." _
            & vbCrLf & vbCrLf & "Check if this is enabled:" _
            & vbCrLf & vbCrLf & "Excel 2003 & XP:" _
            & vbCrLf & " * Tools > Macro > Security > Trusted Sources >" _
            & vbCrLf & "  tick 'Trust access to Visual Basic Project' check box > OK." _
            & vbCrLf & vbCrLf & "Excel 2007 & 2010:" _
            & vbCrLf & " * Click Microsoft Office button > Excel Options >" _
            & vbCrLf & "  Trust Center > Trust Center Settings > Macro Settings >" _
            & vbCrLf & "  tick 'Trust access to the VBA project object model' check box > OK." _
            , vbInformation, s3 & " installed or registered?"
          Exit Sub
        End If
                    
        ' loop through all VBProject References
        For n = 1 To ActiveWorkbook.VBProject.References.Count
          
          Select Case ActiveWorkbook.VBProject.References.Item(n).Type
            Case 0: s1 = "TypeLib"
            Case 1: s1 = "Project"
          End Select
          
          'ERROR NOTE: Name, Description & FullPath can cause: Method 'Name' of object 'Reference' failed
          .Cells(n + 1, 1) = n
          .Cells(n + 1, 2) = ActiveWorkbook.VBProject.References.Item(n).Name
          .Cells(n + 1, 3) = s1
          .Cells(n + 1, 4) = ActiveWorkbook.VBProject.References.Item(n).Description
          .Cells(n + 1, 5) = ActiveWorkbook.VBProject.References.Item(n).IsBroken
          .Cells(n + 1, 6) = ActiveWorkbook.VBProject.References.Item(n).Major
          .Cells(n + 1, 7) = ActiveWorkbook.VBProject.References.Item(n).Minor
          .Cells(n + 1, 8) = ActiveWorkbook.VBProject.References.Item(n).GUID
          .Cells(n + 1, 9) = ActiveWorkbook.VBProject.References.Item(n).FullPath
          .Cells(n + 1, 10) = ActiveWorkbook.VBProject.References.Item(n).BuiltIn
          
          ' Check if mscomct2.ocx is installed &/or registered so DTPicker recognised
          ' (Microsoft Windows Common Controls-2)
          If ActiveWorkbook.VBProject.References.Item(n).GUID = s2 Then
            ' NOT installed
            If ActiveWorkbook.VBProject.References.Item(n).IsBroken = True Then
              MsgBox s3 & " (Microsoft Windows Common Controls-2)" _
                & vbCrLf & "is not installed (or registered). " _
                & "This is required for the date selection objects to be usable." _
                & vbCrLf & vbCrLf & "1) Download mscomct2.cab from http://activex.microsoft.com/controls/vb6/MSCOMCT2.CAB" _
                & vbCrLf & "2) Unzip/extract mscomct2.cab (.ocx & .inf)" _
                & vbCrLf & "3) Copy extracted files to relevant directory as administrator (click continue if prompted for admin permission)" _
                & vbCrLf & "  c:\windows\system\  = Windows 95, 98, or ME" _
                & vbCrLf & "  c:\WINNT\system32\  = Windows NT or 2000" _
                & vbCrLf & "  c:\windows\system32\ = Windows XP or 7" _
                & vbCrLf & "  c:\windows\sysWOW64\ = Windows 7 64bit" _
                & vbCrLf & "4) Close & re-open spreadsheet." _
                , vbCritical, s3 & " missing!"
            ' registered
            ElseIf InStr(0, ActiveWorkbook.VBProject.References.Item(n).FullPath, s3) > 0 Then
              'do nothing
            ' NOT registered
            Else
              MsgBox s3 & "(Microsoft Windows Common Controls-2)" _
                & vbCrLf & "is not registered. " _
                & "This is required for the date selection objects to be usable." _
                & vbCrLf & vbCrLf & "Register it by doing ONE of the following &" _
                & vbCrLf & "close & re-open spreadsheet." _
                & vbCrLf & " * Start > Run > regsvr32 [fullpath]/mscomct2.ocx" _
                & vbCrLf & " * Start > Programs > Accessories >" _
                & vbCrLf & "  right click 'Command Prompt' >" _
                & vbCrLf & "  Run as administrator > regsvr32 [fullpath]/mscomct2.ocx" _
                & vbCrLf & vbCrLf & "Full paths:" _
                & vbCrLf & "  regsvr32 c:\windows\system\  = Windows 95, 98, or ME" _
                & vbCrLf & "  regsvr32 c:\WINNT\system32\  = Windows NT or 2000" _
                & vbCrLf & "  regsvr32 c:\windows\system32\ = Windows XP or 7" _
                & vbCrLf & "  regsvr32 c:\windows\sysWOW64\ = Windows 7 64bit" _
                , vbCritical, s3 & " not registered!"
            End If
          End If
          
        Next n
        .Cells.EntireColumn.AutoFit
        
      End With
      
      Exit Sub
      
    End Sub
    [/code]

    • Marked as answer by Bessie Zhao Thursday, July 01, 2010 8:11 AM
    Monday, June 28, 2010 4:25 PM
  • Indeed! Why were these controls removed?! Answer, please!

     

    Tana-Lee


    New Samsung Blackjack II / Windows Mobile 6.0 user
    Sunday, July 11, 2010 10:59 PM
  • Have you totally exhausted your attempt to use calendar date picker?  I am attempting to deploy an expense report to large staff with diverse releases of office/widows and am encountering the same issues. 

     

    TIA 

    Friday, July 16, 2010 4:44 PM
  • Here is a Excel-ready (no coding required) app solution from WinCalendar - it integrates with both Microsoft Excel and Word, and yes it is free :-)

    Free Excel Pop Up Calendar

    http://www.wincalendar.com/excel-pop-up-calendar.html

    Friday, December 03, 2010 2:03 AM
  • So I've also been tormented by this wonderful surprise of Office 2010 as well as a few others.

    I have many existing Excel files that I had built with the Calendar Control 12.0, and I and the people that used the files loved the functionality. But then we upgraded, why, why, why does an upgrade mean soooo much trouble and extra work tracking down these issues? I mean it's not like it was Calendar Control 3.0 or something super out-dated.

    Microsoft please think of how you can take steps and help us avoid this kind of thing in the future. Maybe your "Customer Experience Improvement Program" could start tracking the References and Controls that are being used and then you can ensure that these are included with each release. Just and idea.

     


    profile for Daniel_DeHaven at Stack Overflow, Q&A for professional and enthusiast programmers
    Sunday, December 19, 2010 4:55 AM
  • What a complete cluster-****!
    No MS datetime picker or calendar control that works in 64-bit!
    How the hell are those of us who wrote code and have working documents that use those controls supposed to move forward?
    My office just bought dozens of computers pre-loaded with Office 2010 (64-bit) and the 50-or-so office-wde-used Excel workbooks with pop-up calendars have just become a write off.
    LOVELY PLANNING AND SUPPORT MS!
    Saturday, November 12, 2011 10:19 AM
  • What a complete cluster-****!
    No MS datetime picker or calendar control that works in 64-bit!
    How the hell are those of us who wrote code and have working documents that use those controls supposed to move forward?
    My office just bought dozens of computers pre-loaded with Office 2010 (64-bit) and the 50-or-so office-wde-used Excel workbooks with pop-up calendars have just become a write off.
    LOVELY PLANNING AND SUPPORT MS!

    That's right!

    In Windows XP there's no any little problem about that library. But in Windows 7 Home Basic (64-bit) I just failed to register it:

     For Windows XP or 7:
     regsvr32 \windows\system32\MSCOMCT2.OCX

      You should see a message saying "DllRegisterServer ... succeeded"

    Instead of this message I receive the diagnostic screen telling me that it just 'Failed to load module \windows\system32\MSCOMCT2.OCX'. I suppose that happens because of some auxiliary dependencies in the library that are not present in Windows 7. What should I do? Rewrite all software or give up with Windows 7? Or there's still some workaround? Anyway, that's really a headache. Maybe someone knows a solution? (for Windows 7 not XP!)

    Monday, January 02, 2012 8:42 PM
  • Yeah, It's gone in every version of Windows 7 that we have loaded.

    It’s been a while but I believed that I gave up looking for a work around and just made a field/control validated and use some code to ensure the dates were within certain ranges. Also fairly easy to pass a default in as well.

    Basically it was one step forward, two or three back. Real bummer as that was a great control. I just think it's sad that MS didn't ship an alternative or "New and Improved" version. They just cut it out altogether.


    FYI, Microsoft, 28K views (to-date) on this thread. You think maybe a few of us used this control? Maybe a good idea to ship it in the next SP? Thanks, Us
    • Edited by Daniel_DeHaven Monday, January 02, 2012 9:05 PM Clarification to MS
    Monday, January 02, 2012 9:02 PM
  • Some related posts :) 

    * Date/Time/Calendar Control to work in multi Excel platforms (2003/2007/2010)?
      http://www.mrexcel.com/forum/showthread.php?t=477358

    * Problem with Date/Time Picker in Excel 2000
      http://www.mrexcel.com/forum/showthread.php?t=70813&page=2

    * Multi-compatible Excel xlsm - how to solve object/activex/reference issues:
      http://www.xtremevbtalk.com/showthread.php?t=316914

    * Excel - Date & Time Picker / Calendar Controls - multi compatible ???
      http://social.msdn.microsoft.com/Forums/en/exceldev/thread/c7ccebe5-5d7f-4fb2-ae16-1438e0e736a4

    * Calendar Control DLL on Windows 7 64 Bit
      http://www.dailydoseofexcel.com/archives/2010/05/28/calendar-control-dll-on-windows-7-64-bit/
    Regards Shell SQL Server 2000/2005
    Tuesday, January 03, 2012 1:27 AM
  • This is what actually worked in the end for me.

    https://sites.google.com/site/e90e50/calendar-control-class

    • Proposed as answer by David Powell1 Monday, May 14, 2012 11:44 PM
    Wednesday, February 29, 2012 7:37 PM
  • There is no better way to assure compatibility than to have the code.  The little team who wrote this spent a lot of effort making their replacement conform to the original methods and behaviour.  Beyond that, the help page is a model of simplicity to follow.  Nothing has been left out.  Because you're adding a VBA class and not an ActiveX or OCX, there are no issues of registration on other machines, or hassles with respect to the word size of your Office version.

    Thanks, "TapeToTape" - a pot of gold at the bottom of the well!

    And thanks, Krisztina, Szabó,Gábor Madács and Roberto Mensa for its creation!


    Monday, May 14, 2012 11:55 PM
  • @David Powell

    thank you very much, your words are beautiful and encourage us to continue :-)

    r, Kris, Gabor (Frankens Team)

    Friday, June 08, 2012 9:44 AM

  • I made calendar control (for one or multiple months). Without having the active x controls available.

    Just try here:

    http://users.telenet.be/bulevardi


    http://www.bulevardi.be

    Monday, January 07, 2013 1:28 PM
  • How do you make this work in one cell. This double click anywhere on the sheet is nonsense? The end result is good, but can't deal with double click, need specific location?


    heads up


    Tuesday, January 07, 2014 1:58 PM
  • Hi, texastwostep,

    Do you want a double-click to conditionally fire up the calendar control, depending on which cell is active?

    In other words, you would like a double-click to be your trigger, just on the cells that apply (rather than a button, as the date picker example shows)?

    On a spreadsheet tab, right click "View Code".  This gives a different view from doing Alt-F11.  You get a different set of event procedures, at spreadsheet scope, which are useful for what you want to do.

    Here's the skeleton for one of them:

    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    End Sub

    "Target" will supply the active range at the time the double-click is executed (probably a single cell).

    You can limit the datepicker's  effect based on some test that you devise to identify the applicable cells. 

    I've done something similar with Worksheet_SelectionChange().  Haven't directly tested the above.

    Excel 2010.

    Good luck

    David

    Saturday, January 11, 2014 10:24 AM
  • Hi, did all this and still get an error message Can not input object. any ideas?
    Tuesday, May 20, 2014 1:06 PM

  • Re:  Date Picker

    A couple of alternatives are presented here (including mine)...
    http://answers.microsoft.com/en-us/office/forum/office_2013_release-customize/month-view-calendar-cotrol-alternate-if-relevant/685debf9-539d-47c1-9a46-9e7337308ffd#LastReply
    '---
    Jim Cone
    Portland, Oregon USA
    https://jumpshare.com/b/O5FC6LaBQ6U3UPXjOmX2

    Tuesday, May 20, 2014 1:43 PM