FOUR ELMS BOOKKEEPING
Mudford, Yeovil, Somerset
WHAT HAPPENED TO THE DATE-TIME PICKER IN SOME RECENT RELEASES OF MICROSOFT EXCEL?
Backward compatibility with earlier versions of Excel is essential if macro-enabled workbooks created in earlier releases of
Excel are to continue working in Excel 2016 or later. But are Visual Basic for Applications UserForms and code
written in Excel 2010, Excel 2007 or Excel 2003
guaranteed to work in Excel 2016 or Excel 2013? Not always, it seems!
Most code written for the Microsoft Operating System relies on dynamic link libraries produced by Microsoft.
The Visual Basic code is like the top layer of a stack which calls up and executes DLL or OCX files resident in the Windows operating system.
Remove even one element of the supporting framework and the top level code ceases to function. This can cause compatibility issues when
Visual Basic for Applications (VBA) modules are imported into later versions of Excel than the one in which they were originally written: dynamic
link libraries or files needed by the VBA code are sometimes retired from the Excel DLL support framework causing the VBA modules to stop working.
After upgrading to Microsoft Office 2016 in February 2017 one of the first things I noticed is that many VBA Userforms which I had created for the input of accounting data no longer worked.
The error messages from the Visual Basic compiler were a bit cryptic: the offending forms were declared not to exist despite the fact that they were displayed correctly
on the screen in the IDE form designer. A line by line debug of the form revealed that this perplexing message was triggered by one particular Active-X control:
the date-time picker. What the compiler actually meant was: "the date-time pickers on the form cannot be created" but the closest it could get was to declare
the form non-existent. Compiler error messages are not always straightforward or easy to understand!
Figure 1: Date Time Picker
The date-time picker is a very useful control on forms. Not only does it facilitate the input of dates by users,
but its calendar interface overcomes the common confusion between
US-style and UK-style date formats when dates are input textually as a combination of numerals and backslashes.
On the left is a picture of this control in action automatically
highlighting the default date on which I used this control. It allows the user to move easily one month or a whole year
backwards or forwards from the current date.
Dates can be entered quickly and accurately: no-one used to this well-designed Active-X control would like to revert to entering dates via the keyboard as text.
This made it disappointing that Microsoft had apparently removed this Active-X calendar tool,
but not replaced it by any equivalent date picker widget.
When I first installed Office 2016 on Windows 10 Professional, Excel 2016 was therefore missing an indispensable Active-X control which it enjoyed in the past.
Later I did discover this ActiveX control on a different Windows 10 Office 2016 install, so maybe Microsoft are now continuing
to provide this useful tool after all. But what can one do if, like me, one finds this useful date picker is no longer available within the
Excel 2016 Developer IDE?
The file which creates the date-time picker is called mscomct2.ocx and used to live in the
C:\Windows\SysWOW64\ directory. If you are about to upgrade from Excel 2003, 2007 or 2010 to Excel 2013 or
later then you may wish to keep a copy of this file so that you can continue to use it after your upgrade. Unfortunately
this file is no longer available for download from Microsoft and only unofficial sites still offer this file for download.
The process of making this control available
in a later version of Office is not entirely straightforward and precautions are advisable to safeguard your Windows registry
and operating system in case you make a mistake.
The date-time picker is an old VB6 control with a long service life stretching back to the days before .NET
(Visual Basic 6 was released in 1998 but has now been retired.).
As such it is a 32 bit legacy executable. When Microsoft moved to 64 bit operating
systems it divided the dynamic link (DLL and OCX) files between two system folders.
DLL files intended for 64-bit architecture were placed in the C:\Windows\System32\ folder
whereas legacy 32-bit DLL or Active-X files got the C:\Windows\SysWOW64\ folder.
(The names of the folders can be confusing: the 32-bit DLLs go in the folder with 64 in its name
while the 64-bit DLLs go in the other one with 32 in the name.) Putting a DLL in the correct folder is
important. If we place our mscomct2.ocx Active-X control in the SysWOW64
folder the Windows operating system will know that it is a 32-bit executable and make the necessary
adaptations to run a 32-bit executable within a 64-bit operating system.
To get a legacy date-time picker working in Excel 2016 we need to do the following:
- Obtain, copy from an old machine or download a copy of mscomtl2.ocx to the desktop or downloads folder of the machine running Excel 2016.
- Running the machine as administrator, copy mscomtl2.ocx to the C:\Windows\SysWOW64\ folder.
- Register the newly imported mscomtl2.ocx executable with the Windows Registry. In case you make a mistake, you are strongly advised to
make a backup of your Windows Registry
before carrying out this step. This step also requires administrator access to the computer.
PLEASE NOTE: The information in this article is provided in good faith and describes a technique successfully used
by the author on a machine running 64-bit Windows 10 Professional operating system and Excel 2016. The same technique also
enabled user forms containing date-time pickers to be created in Excel 2007 running on Windows Vista. However Four Elms Bookkeeping accepts no liability
for any malfunctioning of your computers that may result from readers attempting to follow the instructions or advice in this article