Using The New Date Picker In Excel 2010
Note: The process below will only work for Office 2010 32-bit (not Office 2010 64-bit).
Microsoft discontinued the legacy 32-bit calendar control, MSCAL.OCX, in Excel 2010 and replaced it with the new 32-bit Date Picker contained in Active-X library member mscomct2.ocx.
Important Note: The Active-X Date Picker can be registered for Office 2010 32-bit, but it will not work on Office 2010-64-bit. Developers who use the Date Picker are advised to stay with the 32-bit version of Office 2010. There is no official replacement for the much-loved date picker in the 64-bit version of Office 2010, as shown in the following article and forum post by Microsoft:
Date Picker Active-X Control Only Works On 32-Bit Office 2010
No Formal Date Picker For Office 2010-64bit As Of July 2012
If you are using Office 2010 32-bit, then the following procedures will work.
If the date picker library is not already registered, then :
FOR 64-BIT WINDOWS 7, HERE'S HOW:
(1) Perform the function below on any computer that is missing the mscomct2.ocx.
(2) Make sure a copy is in C:\Windows\SysWoW64
(3) Click the Windows 7 Start Button and in the Search area, type "command"
(4) The search will bring up a number of items, including "Command Prompt" at the top
(5) Right click the "Command Prompt" banner, and select "Run as Administrator"
(6) At the command prompt, enter: %systemroot%\SysWoW64\regsvr32.exe mscomct2.ocx
(7) This should successfully register the control.
FOR 32 BIT WINDOWS 7, HERE'S HOW:
(1) Perform the function below on any computer that is missing the mscomct2.ocx
(2) Make sure a copy is in C:\Windows\System32
(3) Click the Windows 7 Start Button and in the Search area, type "command"
(4) The search will bring up a number of items, including "Command Prompt" at the top
(5) Right click the "Command Prompt" banner, and select "Run as Administrator"
(6) At the command prompt, enter: %systemroot%\System32\regsvr32.exe mscomct2.ocx
(7) This should successfully register your legacy mscomct2.ocx
Once The Control is registered, it can be added to the Control Toolbox available in the VBA area of Excel:
Select the date picker control from the list of available Active-X controls:
Once it is selected, it can be used from the toolbox:
The control uses the "Change" event as shown below:
Program Code
Private Sub DtPicker1_Change() ' ******************************************************** ' Update Date Based on Calendar Click ' ******************************************************** dteDateSelected = DTPicker1.Value End Sub