Effective Calendar Solutions

[Joseph]

I currently have a workbook, that is to be distributed to 40 different sites across the US. Some of these sites are quite remote and they are not able to connect to the server that I have set these reports up on. Therefore they are completing the reports locally and emailing them back to me.

Now I have several different versions of Excel trying to utilize, and I'm running in to calendar issues (Object not found on this computer). My question is. Which date picker from which object library would have the most success in operating on different versions? It seems now, I'll have to build several templates with different calendars. An easier solution is needed.

[HansV]

To avoid problems with ActiveX controls, it's best to use a calendar built entirely from "native" Excel controls. This will work in all versions, since it doesn't refer to external files. You can find one at VBA Express : Excel - Userform Calendar Control (download link near the bottom of the page).

[agibsonsw]

That's a nice calendar.
Does this mean that the userform and it's controls are VB(A) controls, not ActiveX controls? But if I add a Calender Control to a userform this IS an ActiveX control?
Is there any easy way to distinguish? Andy.

[Joseph]

Perfect!!! Thank you so much Hans!!! Works a treat!!

[HansV]

When you install Microsoft Office, the Toolbox contains only the MSForms controls (label, text box, combo box, etc.) They all use a DLL (FM20.DLL) that is included and installed with every version of MS Office.
To make other controls available, you right-click an empty part of the Toolbox and select Additional Controls... from the popup menu. If you select an item, for example the Calendar control, you see the file it uses - in this example MSCAL.OCX:

x447.png

The .ocx extension tells us it's an ActiveX control. But the crucial point is that the Calendar control is not part of every version of MS Office - it comes with MS Access, so it is only included in Office Professional or higher, not in the more basic versions.

If the userform is for your own use only, and if you have Access, that's fine, but if you need to distribute it to others, you will almost certainly run into problems. A solution that uses MSForms controls only will not cause such problems.

[agibsonsw]

Thank you for this explanation.