Including New Controls within an Excel file

  • I recently installed the Date and time picker control from Microsoft for Excel, i am using Excel 2016.

    This works fine with no issue, and the usage is also not a problem, but when i email the file to colleagues they are getting an error message about a missing control which could be resolved by installing the date and time picker on the other PC, but as the file will end up going to many colleagues i am looking for an alternative solution. The DTP is used within a userform should this make any difference.

    Is there a way of including the Date and time picker control within the file being distributed.

    Thanks in advance to anyone trying to assist.

  • Unfortunately this is because the Datepicker control requires the mscomct2.ocx ActiveX control module to be installed on each target PC and that requires admin rights to install.

    If you hunt around you will see numerous people have devised alternative date pickers, a number of them on this forum.

    Over the years I have refined my own version as well although it requires a collection of modules (including class modules) and forms in order to work. I've attached 2 variants of mine - 1 showing how to use it on a userform and the other is within a cell on a worksheet. The same code underpins both, they've been separated to show the different techniques required to show a dropdown calendar on a worksheet versus a userform. The last change I made to this was to auto-close the datepicker when the mouse moves outside the calendar form.

  • To both gijsmo and royUK many thanks for the suggestions and options made available.

    I actually ended up doing something similar to what you are suggesting Roy, but in my case a little easier as for this case we are only interested in the last day of the month, so a simple drop down for month, and a second for year allows me to easily define the day where required during hte printing phase.

    Much appreciated guys.