Calendar Control for All Office versions - including Office 2010 64 bit



                Some feedbacks we received from our users!



Missing Calendar Control

Calendar Control - for one reason or another, this is not part of the basic VBA form component kit. Microsoft manages Calendar Control in a separate way, in an own .ocx package - it is usually installed with Microsoft Office.
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!

Ingredients:

  • 41 buttons and label boxes

  • 2 pieces of combo boxes

  • 8 piece of label box

  • Approx. 1300 row VBA code

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!


Now, seriously....
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.

Authors:
Roberto Mensa
Gábor Madács
Krisztina Szabó

Send your comments


Calendar Class Project

The concept

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):

    Private WithEvents Calendar1 As cCalendar

(WithEvent is allows us to catch the Click and other events of the Control.)

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:

Private Sub UserForm_Initialize()

    Set Calendar1 = New cCalendar

    Calendar1.Add_Calendar_into_Frame Me.Frame1

End Sub




The Calendar functionality is built up from buttons, so the grid-related properties can not work.

Development experiences

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.

Test environment

We used the below form to test how the calendar works.



Technical details - Reference

Properties

Original Calendar Control properties
Parameter Data Type Description
BackColor Long Configures the background color of the Frame.
ControlTipText String Specifies text that appears when the user briefly holds the mouse pointer over a control without clicking.
Day Long Gets the current day of the month.
DayFontColor Long Configures the color used to display the days of the week.
DayLength Long Sets the format used to display the days of the week: 0 for Long (“Monday”), anything else for Short (“M”)
FirstDay calDayOfWeek Sets the day of the week to be displayed in the first column: 1 = dwMonday, 2 = dwTuesday… 7 = dwSunday
GridFontColor Long Specifies the color used to display the days of the month.
Month Long Sets the current month. Must be a value between 1 (January) and 12 (December).
MonthLength Long Configures the format used to display the month:
Enumerations:
mlLocalLong = 0 for local language long (szeptember)
mlLocalShort = 1 for local language short (szept.)
mlENLong = 2 for English long (September)
mlENShort = 3 for English short (Sep)
ShowDateSelectors Boolean Specifies whether the calendar will display drop-down boxes for the month and year. A value of -1 means True and a value of 0 means False.
ShowDays Boolean Specifies whether the calendar display the days of the week. A value of -1 means True and a value of 0 means False.
ShowTitle Boolean Specifies whether the calendar display the month and year above the calendar grid. A value of -1 means True and a value of 0 means False.
TitleFontColor Long Determines the color used to display the month and year above the calendar grid.
Top Single The distance between a control and the top edge of the form that contains it, in points.
Height Single The height of the control, in points.
Width Single The width of the control, in points.
Left Single The distance between a control and the left edge of the form that contains it, in points.
Value Variant Sets the currently selected date.
ValueIsNull Boolean Specifies whether the value is Null (that is, no data is selected).
Visible Boolean Specifies whether the control is visible or hidden.
Year Long Sets the current year.
Tag String A user-defined string value.
TabIndex Long Specifies the position of a single object in the form's tab order
TabStop Boolean Indicates whether an object can receive focus when the user tabs to it.



New properties for Calendar Class only


We created some new properties we think would be useful:


UseDefaultBackColor Boolean Specifies whether it is possible to use different color for the weekends and header (False) or not = all days has the same color, like in the original Calendar Control (True)
HeaderBackColor Long Configures the background color of the Header (days of the week)
SaturdayBackColor Long Configures the background color used to display Saturdays.
SundayBackColor Long Configures the background color used to display Sundays.
YearFirst Boolean Determines the order of year and month in the label above the days. True means text begins with year: 2011 dec. False means text begins with month: dec 2011





Font properties with special change method
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...

DayFont Font Configures the font used to display the days of the week.
GridFont Font Specifies the font used to display the days of the month.
TitleFont Font Sets the font used to display the month and year above the calendar grid.

Not working properties
The following properties are created to avoid migration errors but they are not working:
GridCellEffect Integer Determines the effect used to display the grid.
GridLinesColor Color Determines the color used to display the lines in the grid.
ShowHorizontalGrid Boolean Specifies whether the calendar display horizontal gridlines.
ShowVerticalGrid Boolean Specifies whether to display vertical gridlines.
HelpContextID


Methods

Aboutbox() You can see the name of the authors :-)
Add(ByVal fForm As MSForms.UserForm) Adds a new frame together with the Calendar Class to your form.
Add_Calendar_into_Frame(ByVal cFrame As Control) Adds the Calendar Class to your existing frame.
Move([Left], [Top], [Width], [Height], [Layout]) Moves/resizes the frame within the form.
(Layout parameter is ignored.)
NextDay()
NextWeek()
NextMonth()
NextYear()
PreviousDay()
PreviousWeek()
PreviousMonth()
PreviousYear()
Changes the value property of the Calendar class.
Refresh() Refreshes the Calendar.
(You need to call this method only if you want to change the font styles at runtime.)
Today() Sets the Value property of the Calendar Class to the actual date.

Events

Click Occurs when clicking on the calendar days.
DblClick Occurs when the user points to a day in the grid and then clicks a mouse button twice.
BeforeUpdate(ByRef Cancel as Integer) Occurs before changed data in the control.
Setting the Cancel argument to True (–1) cancels the ongoing change.
Note: This event is not working in the original Calendar Contol
AfterUpdate Occurs after changed data in the control.
Note: This event is not working in the original Calendar Contol


Please note, the following events will not work in the Calendar Class:
Enter, Exit, KeyDown, KeyPress, KeyUp, NewMonth, NewYear


How to migrate Calendar Class into your existing project?

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.

Preparation

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.

Change the code manually

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:


Private WithEvents Calendar1 As cCalendar


2.3. Create Calendar object in the form code, and add to the frame:

Private Sub UserForm_Initialize()

      Set Calendar1 = New cCalendar

      Calendar1.Add_Calendar_into_Frame Me.Frame1

End Sub


(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.

Changing of the default settings

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.

Private Sub UserForm_Initialize()

      Set Calendar1 = New cCalendar


 With Calendar1


 <copy here the non-default properties>


 End With


      Calendar1.Add_Calendar_into_Frame Me.Frame1

End Sub


For example:

Private Sub UserForm_Initialize()

      Set Calendar1 = New cCalendar


 With Calendar1

    .YearFirst = True ‘Label format: 2011 dec

    .UseDefaultBackColor = False ‘Use advanced coloring

 End With


      Calendar1.Add_Calendar_into_Frame Me.Frame1

End Sub

Change the code with the help of the Migration routine

We created a great helper-code to collect all of the properties where the property value is not the default and create a specific setup subroutine you only need to copy into your code. You can use it only in old, before-2010 Office, where you have a living Calendar Control.

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:

Private Sub UserForm_Initialize()


<your existing code lines>

...

<your existing code lines>


Debug.Print Migration_of_Calendar_Classes(Me)


End Sub


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.

Important note - bug in Office 2010 64 bit

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.
E.g.:

...

myCalendar.DayFont.Italic = True

myCalendar.GridFont.Bold = True

myCalendar.GridFont.Italic = True

myCalendar.Refresh

...

Very simple usage example - Date Picker

Create an Excel Worksheet Date Picker!

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:

Option Explicit

'UserForm with Frame1

Private WithEvents Calendar1 As cCalendar


Private Sub UserForm_Initialize()

Set Calendar1 = New cCalendar

Calendar1.Add_Calendar_into_Frame Me.Frame1

End Sub


Private Sub UserForm_Activate()

Calendar1.Value = ActiveCell.Value

End Sub


Private Sub Calendar1_DblClick()

ActiveCell.Value = Calendar1.Value

Unload Me

End Sub


Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

Set Calendar1 = Nothing

End Sub


Now we can create a Command Button on a workseet with this code:

Private Sub CommandButton1_Click()

UserForm1.Show

End Sub


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.

3/3 :-)

Other controls

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.



Creative Commons License

UPDATES

Since the debut of the Calendar Control we got some feature requests from our dear users.

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:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   

    If Not Intersect(Range("A6,C6"), Target) Is Nothing Then
        '----------------------------------
        ' Open DatePicker
        Set DatePickerForm.Target = Target.Cells(1, 1)
        DatePickerForm.Show vbModal
        Cancel = True
        '----------------------------------
    End If

End Sub


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 adressing you should hard-code the addresses every time. But you would like to use a more dynamic method, for examlpe you can control the Date Picker by colororing 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:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   

    If Target.Cells.Count = 1 Then
        If Target.Interior.ColorIndex = 6 Then
            '----------------------------------
            ' Open DatePicker
            Set DatePickerForm.Target = Target.Cells(1, 1)
            DatePickerForm.Show vbModal
            Cancel = True
            '----------------------------------
        End If
   
End If

End Sub

Now the 6 is for the yellow color. (You can check other actual Target.Interior.ColorIndex codes with the debugger.)

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.)

Calendar Control v1.1.0 DatePickerOnly v2 - DatePicker Popup Advanced Positioning

It is a simple change in the DatPicker form: The window appears near the clicked cell, and never out of the sceen.
Regardless of the Application or the Active Window is maximized or moved away or maximized.

Calendar Control v1.1.0 - with simple DatePicker Popup Form!

Maybe there are more people who missed this: introducing a ready-to use, DatePicker Popup-enabled sample file: Calendar_Class_v1.1.0_DatePickerOnly.xlsm

This 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:

Here is a simple example code, we need something like this in an Excel worksheet:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    '----------------------------------
    ' Open DatePicker
    Set DatePickerForm.Target = Target.Cells(1, 1)
    DatePickerForm.Show vbModal
    Cancel = True
    '----------------------------------
   
End Sub

In this case, if the user doubelcicks on any cell in the worksheet, the DatePickerForm appears.

DatePickerForm functionality:

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.


New event:
KeyDown (ByVal KeyCode As MSForms.ReturnInteger,
  ByVal Shift As Integer)
Occurs when user pressing keys - other than the cursor arrows.
(E.g. useful for catch the Esc key:
KeyCode = vbKeyEscape )

Calendar Control v1.0.6

New properties:
SaturdaySelectable Boolean Saturdays are selectable or not.
SundaySelectable   Boolean Sundays are selectable or not.  
New request from our user - disabling the selection of weekend days.

Calendar Control v1.0.5

Small fix: Spelling of "Wednesday" in fixed English day name. (When <cal>.DayLength = mlENLong '2 )

Calendar Control v1.0.4

New property:
RightToLeft Boolean This property mirrors the day labels horizontally.
A big step towards internationaliztion.
Weekdays ordered right to left.

Calendar Control v1.0.3

New property:
SelectedBackColor Color Specifies the background color of the selected day.
This option is left out form the original solution. :)

Calendar Control v1.0.2

New property:
MACFix Boolean Compatibility option for Microsoft Office for Mac.
On Mac the transparent background of the form button component is not working. With this property the button transparency feature can turned off - at the cost of limited minimal size of the day buttons. (The button captions demand a minimal margin.)

<cal>.MACFix = True


Download section

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.
Č
Ĉ
ď
Gábor Madács,
Dec 27, 2012, 12:28 PM
Ĉ
ď
Calendar_Class_v1.0.5.xlsm
(135k)
Gábor Madács,
Dec 27, 2012, 12:28 PM
Ĉ
ď
Gábor Madács,
Mar 22, 2013, 4:42 PM
Ĉ
ď
Calendar_Class_v1.0.6.xlsm
(120k)
Gábor Madács,
Mar 22, 2013, 4:41 PM
Ĉ
ď
roberto mensa,
Dec 21, 2011, 9:45 AM
ċ
ď
Calendar_Class_v1.0.xlsm
(120k)
roberto mensa,
Dec 21, 2011, 9:41 AM
Ĉ
ď
Calendar_Class_v1.1.0.xlsm
(88k)
Gábor Madács,
Mar 11, 2014, 2:07 PM
Ĉ
ď
Calendar_Class_v1.1.0_DatePickerOnly_v2.xlsm
(81k)
Gábor Madács,
Mar 11, 2014, 2:00 PM
ċ
ď
Samle1.xlsm
(901k)
roberto mensa,
Dec 22, 2011, 6:50 AM
Comments

Bibliografia essenziale