Update 2017.04.03. - v2.0.0: We have added a new mayor feature-set: Colored Date Arrays!
Update 2016.10.30. - v1.2.0: A recent security patch removed some features from the common control library,
so the Calendar Control had to be altered to work in the patched Office 2016 applications.
When you upgrade your Office version to the new Office 2010 you can face several compatibility problems. After reading this error message: “Could not load an object because it is not available on this machine” you need to find out what is the problem.
First check the references of your VBA project. In the VBA window choose Tools / References for your VBA Project. You may see something like this:
(MANCA = MISSING and in some versions, Calendar Control had local name.)
Now it is clear that the missing mscal.ocx, the Calendar Control is the problem. We googled for a solution and soon found this on Microsoft TechNet ( http://technet.microsoft.com/en-us/library/cc179167.aspx )
“Calendar control (mscal.ocx) is removed in Access 2010 and is not usable for Excel 2010. Instead, users can use Date Picker or their own custom calendar controls.”
So, we have tried Date Picker, and our experience is that it is not easy to use in existing applications. The property and event structure is different from the Calendar Control. In most of the cases it also needs to be installed (it does not exits in Excel 2007).
There are some hack-kind solutions for the 32-bit version Office (by installing the old mscal.ocx on each workstation where you want to use your application) but at the moment 64-bit version of the .ocx does not exist. Maybe never will...
So, what can we do? We can cook one!
Take a Class module, put most of the VBA code into it. Mix it with your application. Take out the decayed items, replace it with the new, freshly-cooked one, served in a simple frame! All of your applications will easily digest it!
We build the control as a class using the very basic elements (buttons, combo boxes, label box) for avoid the problem of compatibility. Forever. (Knock on wood!)
So, we developed a non-ActiveX, non-MSCAL.OCX dependent calendar class for VBA UserForms. It means you do not need to install anything. (It use only the MSFORMS.OCX, ) Our Calendar Class will be part of your application. In case you share your file with anyone else, it will work on his/her computer without any system changes. The only thing to do is to migrate the Calendar Class into your existing application, and use it in your future projects.
It was a huge project, a tremendous thing for us. We spent approx. 20 days with it, mainly the late-night hours. The team-work was very effective, rousing and funny - we really enjoyed it! We are stopping now at version 50 - this works well for almost all of the cases. If you see any problem or inconvenience please do not hesitate to contact us!
You can find the files at the bottom of this page.
Our Calendar Class works in all version of Office VBA (Excel, Word, PowerPoint, Outlook)
In the below tables you can find all of the Parameters of the Calendar Class. To ensure compatibility, parameter names are exactly the same as it was in the Calendar Control.
Our goal was to create only one Class module to make the users easier to import and migrate the new Calendar Class.
The original Calendar Control is an ActiveX Control, has to be referenced in the project, and it can be added to the from via the component toolbox. The Calendar Class itself can not be added directly to the form, we need to put it to the source, as a new member of the form (top of the source page, before any subs or functions):
For convenient visual placement and size definition Calendar Class should be placed into a new, own frame instance on the form - for example:
This frame should be referenced in the Form_Initialize() method after creating the Control object, for above example:
The Calendar functionality is built up from buttons, so the grid-related properties can not work.
The aim of the project was to create a Class with exactly the same properties, methods and events as it is in the Calendar Control. We decided to use buttons to represent the days. It turned out soon that this solution does not allow to create a small sized calendar because the position of button caption is pre-defined within the button. So we changed the concept a bit and put label boxes behind the buttons.
Certainly with this button-solution we do not have Grid - this is the reason why some property will not work - however all of them created.
We used the below form to test how the calendar works.
New properties for Calendar Class only
We created some new properties we think would be useful:
In this version of Calendar Class it is difficult to change the font setting at runtime, so we suggest to use these properties only in UserForm_Initialize. If you need to do the change at runtime, see section under How to migrate...
The following properties are created to avoid migration errors but they are not working:
Please note, the following events will not work in the Calendar Class:
Enter, Exit, KeyDown, KeyPress, KeyUp, NewMonth, NewYear
Based on our experience we highly advise you to replace the Calendar Control with Calendar Class before starting to use your project in Office 2010. In this case you can prevent the error messages and you can use our VBA code to collect the special properties of your old Calendar Control and build up the necessary subroutines - see under section 4.
In case you already use Office 2010 and you do not have Calendar Control, you will see in your VBA Project References that Microsoft Calendar Control is missing (MANCA = MISSING) (In some versions, Calendar Control had local name.)
You have to tick out the missing control - it will not touch your existing code. Now you can migrate the new Calendar Class into your project as it is explained below.
1.1. Copy the Calendar Control Class Module into your project.
Open your project and the Calendar Class file, copy (drag-and-drop) the cCalendar Class module into your project. (This copy works between two different Office applications too!)
1.2. Now you have a new class module in your project with name cCalendar. You can change the name in the Properties window if you want. In this case, you have to use this new name in the following codes instead of cCalendar.The following steps should be done for all of your calendar control instances (as many calendar controls you have in your form).
In this example we use the default name of Calendar Control: Calendar1. If you gave your calendar a different name, please replace Calendar1 with the name of your calendar in the below code rows.
2.1. Create placeholder frame
You need to create an empty frame control on your form where the Calendar Class will be placed. Move and resize the frame to the desired place of the Calendar.
You can change the name of the frame in the Properties window if you want. In the below code rows the frame is named Frame1.
2.2. Create object reference in the form code. Use the original Calendar Control’s name.
Write to the top of the code window of the userform:
2.3. Create Calendar object in the form code, and add to the frame:
(If you re-named your frame, please write the exact name instead of Frame1)
If you used the Calendar Control with default settings, you do not need more changes in your code.
In case you want to change the default settings, you have to use the following code to create the Calendar object. Use this code instead of the shorter version mentioned under section 2.3 above.
Copy the Migration module from our file into your project.
On your old form, where your old calendar control(s) is placed, add to the very-end of the UserForm_Initialize subroutine a function call for the Migration_of_Calendar_Classes(fUserForm As Object).
For example this way - Me stands for your userform:
In this case you will have the code in the immediate window. The only thing you need to do is to follow the commented instructions.
Regarding Font changes please take into consideration that in case you add a control (in this case a Command Button) to your form at runtime (in Userform_Initialize) the Font.Underline and Font.Strikethrough property settings does not work. You can see on the below picture that the False setting does not change the button caption however other Font properties (bold, italic, etc) works correctly.
After UserForm_Initialize, when the form is fully created, the Font.Underline and Font.Strikethrough change works correctly. The below picture illustrates it with a Commandbutton2_Click event.
How to change font styles at runtime (if needed)
We use as simple components as possible, thus we can not simulate every aspect of the original Calendar Control. Changing the font styles dynamically after the call of Add_Calendar_into_Frame method is a special issue, the changes has to be applied explicitly by the Refresh method.
There will be a single button for show the Date Picker, always editing the active cell.
1. Open VBA macro editor
2. Insert a UserForm into your Excel project (Let’s call it UserForm1)
3. Create a Frame on UserForm1, with name Frame1
4. Copy this code into the code window of UserForm1:
Now we can create a Command Button on a workseet with this code:
Now when the button is pressed, the form with the calendar appears. If the active cell contains a date the calendar will show this date. If the selected cell is empty, the calendar displays the current date. We can change the year and month. With double click on the desired day button, the new date entered into the active cell, and the form disappears.
The main concept of our project was to develop a solution could step over the compatible errors using only the native Office forms controls. We found the same concept used by Jan Karel Pieterse in his MSForm based Treeview control, so we would like to share his tool here - sure it would also be useful for the users of our Calendar Control.
Since the debut of the Calendar Control we got some feature requests from our dear users.
We have added a whole new feature:
Colored Date Arrays:
Rules for Selectable dates:
Please find the Macro of the Colored Date Array usage test in the Release file:
'Start by te button
Public Sub DateArrayTests()
Dim r As Range
Dim dateArr() As Variant
Dim Selectable As Variant
Dim idx As Long
'Make Saturdays and Sundays unselectable.
DatePickerForm.Calendar.SaturdaySelectable = False
DatePickerForm.Calendar.SundaySelectable = False
'DatePickerForm.Calendar.WeekdaySelectable = False
'Clear the current values (DatePickerForm is global)
Call DatePickerForm.Calendar.ClearAllColoredDateArrays 'Refresh dates
'Pass dates by Variant array
dateArr() = [MyGreenDates].Value2
'This dates are selectable (Even if on Saturday or Sunday)
Selectable = True
Call DatePickerForm.Calendar.AddColoredDateArray(RGB(200, 255, 200), dateArr, Selectable)
'Pass dates by Range object. Holding the index (No selectable setting (Default Empty): selection not affected!)
idx = DatePickerForm.Calendar.AddColoredDateArray(vbRed, [MyPinkDates])
'Query settings by index
Debug.Print "Color: ", DatePickerForm.Calendar.GetArrayColor(idx)
Debug.Print "Selectable: ", DatePickerForm.Calendar.GetArraySelectable(idx)
'Replace setings by index. Selectable=false - not selectable dates!
Call DatePickerForm.Calendar.AddColoredDateArray(RGB(255, 200, 200), [MyPinkDates], False, idx)
'* Later is stronger: if a Date is in multiple arrays, the later added will be affected.
'Replace color by index
'Call DatePickerForm.Calendar.SetArrayColor(idx, vbBlue)
In a recent Microsoft Windows security patch, the button transparency settings are rendered ineffective. This is the same story as the Mac version you see in v1.0.2.
In the original design of the Calendar Control class we use transparent buttons, because the button's text has a fix minimum 6 pixel upper inset which pushes the text off the bottom when the button is small. (As normal day button in a calendar...) Therefore we put a label behind the button with the text, not affected by the button insets.
We use the MACFix feature for circumvent this problem.
If Microsoft fixes this problem, you can delete the automatic setting:
You can monitor the events related these problems here:
More DatePicker examples - Restriction for specific cells
The method we'd described in Usage in Your existing projects section allows us to open a DatePicker in any cell of the current worksheet if the user DoubleClick on it.
It is a very basic functionality, you may want to restrict the operation to specific cells where you actually using dates.
In this case, you must enclose the datepicker code with an If-Then statement.
In the If condition you can use any of the cell's properties,
most of the time the simplest is the cell address:
In the If condition, we define the accepted Range by the usual addressing methods and the Range() function.
The above "multiple" (listed by commas) range consists of the cells A6 and C6.
(You may use e.g. "B2:B1000" for a column, etc..)
Then we ask Excel to create the intersection of this accepted range and the Target range by the Intersect() function. This will return a Range object only if the Target is inside the accepted range. Else it will return Nothing. We need the case it will return something, so the simplest check is: the result Is Not Nothing. :)
With the above fixed addressing you should hard-code the addresses every time. But you would like to use a more dynamic method, for example you can control the Date Picker by coloring the background of the desired date cells to yellow!
In this case you can create this condition for the date picker to appear only on the colored cells:
Now the 6 is for the yellow color. (You can check other actual
With this method you can "paint" the datepickers anywhere on your sheet, and the user also can see where he or she can expect a date picker popup after clicking! :)
If you put the above Target.Cells.Count = 1 check, you can use this in the simple Worksheet_SelectionChange method! This way the Date Picker always appear when somebody clicks or enter the cell. (We ensure the user clicked only one cell, so he or she may want to type something, not selecting a greater range for copy or such.)
Regardless of the Application or the Active Window is maximized or moved away or maximized.
Maybe there are more people who missed this: introducing a ready-to use, DatePicker Popup-enabled sample file: Calendar_Class_v1.1.0_DatePickerOnly.xlsmThis update is not really for the Calendar Control itself, but here is a simple, reusable popup form which embedding a Calendar Control for ease of use.
The DatePicker sample is available in the normal Calendar Class v1.1.0 release file too.
Usage in Your existing projects:
1. Copy the cCalendar and DatePickerForm modules to your macro-enabled Excel file.
2. Setup the worksheet, where you want to use the DatePicker popup:
In this case, if the user doubelcicks on any cell in the worksheet, the DatePickerForm appears.
After Show, the DatePickerForm validates the input cell, and if date found, loads it.
On succesful date picking (click or Enter) the cell content is overwritten by the new Date. On simple close or Esc, the content is not changed.
<cal>.DayLength = mlENLong '2 )
Weekdays ordered right to left.
Below you can dowload the latest versions of the Calendar Control class, embedded in a simple Excel demo project.
The Calendar Class Source page contains the Visual Basic source of the latest version.
This work is licensed under a Creative Commons Attribution-NonCommercial 3.0 Unported License