Add calendar control to excel 2007 worksheet

These instructions present a solution to display a date entry calendar control next to specific cells when those cells are selected - no menus or command buttons are required. The calendar control allows selection of the year, month, and the day of the month. Clicking in any other cell or double clicking on a day of the month closes the control. The calendar control is always positioned near the cell and so that it is entirely visible.

The Microsoft Calendar control is used to implement this solution. It is an ActiveX control used to input dates using a graphical month display. Using it to input dates is usually easier than typing the date directly into a cell. One of the most common techniques for using the calendar control on a worksheet is to add the control during development, make it invisible, and then show it when needed. If using a user form the control is placed on the form at design time. The problem with these techniques and with the calendar control in general is that there is a unique version installed with each version of Office and, if pre-loaded onto a worksheet or user form in one version of Office, it may not work correctly in another version of Office. The solution is to use late binding and load the control only when needed.

This article provides a VBA solution that works in all versions of Excel regardless of the version of the calendar control installed. The calendar is created only when needed and discarded (versus hidden) when not needed. The solution positions the control so that it is never hidden outside the visible range of cells, and frames the selected cell and calendar input control in blue. Because the calendar control was not installed with Excel prior to Excel 2007, instructions are included at the end of this article describing how to obtain the control.

The solution is provided in three parts: sample code that is placed in any worksheet SelectionChange event handlers to determine when to show the calendar input control, a general code module that contains the support code, and code to add to the ThisWorkook code module. Below is sample code illustrating how to use the solution. The sample shows the calendar input control when any cell in the range A1:D4 or F1:H4 is selected. Note that no calendar contol needs to be added to the worksheet, the code below adds and deletes the calendar control automatically.

[Begin Code Segment]

Private CalendarDisplayed As Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Not Intersect(Target, [A1:D4, F1:H4]) Is Nothing And Target.Address = Target(1).MergeArea.Address Then
        ShowCalendarInputControl Target
        CalendarDisplayed = True
    Else
        If CalendarDisplayed Then
            HideCalendarInputControl
            CalendarDisplayed = False
        End If
    End If
   
End Sub

Public Sub CalendarInputControl_DblClick()

    HideCalendarInputControl

End Sub

[End Code Segment]

Below is the support code that is placed in a new general code module. To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

[Begin Code Segment]

Option Explicit
   
Private Const CalendarInputControlName = "CalendarInputControl"
Private Const CalendarInputFrame1Name = "CalendarInputFrame1"
Private Const CalendarInputFrame2Name = "CalendarInputFrame2"

Public Sub HideCalendarInputControl()

    On Error Resume Next
    ActiveSheet.OLEObjects(CalendarInputControlName).Visible = False
    ActiveSheet.Shapes(CalendarInputFrame1Name).Delete
    ActiveSheet.Shapes(CalendarInputFrame2Name).Delete

End Sub

Public Sub ResetCalendarInputControl()

    Dim Calendar As Object
   
    On Error Resume Next
    ActiveSheet.OLEObjects(CalendarInputControlName).Delete
    Set Calendar = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", Left:=0, Top:=0, Width:=180, Height:=120)
    If Err.Number <> 0 Err.Number <> 1004 Then
       MsgBox "The Microsoft Calendar Control is not installed on this computer."
       HideCalendarInputControl
       Exit Sub
    End If
    Calendar.Name = CalendarInputControlName
    Calendar.Visible = False

End Sub

Public Sub ShowCalendarInputControl( _
      ByVal Cell As Range _
   )

    Dim Calendar As Object
    Dim CalendarFrame As Shape
    Dim CellFrame As Shape
    Dim HorizontalDelta As Double
    Dim VerticalDelta As Double
   
    HideCalendarInputControl
    If Cell.Left + Cell.Width + 5 + 182.5 > ActiveWindow.VisibleRange.Columns(ActiveWindow.VisibleRange.Columns.Count).Left Then
        HorizontalDelta = -Cell.Width - 10 - 182.5
    End If
    If Cell.Top + Cell.Height + 5 + 123 > ActiveWindow.VisibleRange.Rows(ActiveWindow.VisibleRange.Rows.Count).Top Then
        VerticalDelta = -Cell.Height - 10 - 123
    End If
    Set CellFrame = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Cell.Left, Cell.Top, Cell.Width + 1, Cell.Height + 1)
    CellFrame.Name = CalendarInputFrame1Name
    With CellFrame.OLEFormat.Object.ShapeRange
        .Fill.Visible = msoFalse
        .Line.ForeColor.SchemeColor = 12
        .Line.Weight = 2.5
    End With
    Set CalendarFrame = ActiveSheet.Shapes.AddShape(msoShapeRectangle, Cell.Left + Cell.Width + 5 + HorizontalDelta, Cell.Top + Cell.Height + 5 + VerticalDelta, 182.5, 123)
    CalendarFrame.Name = CalendarInputFrame2Name
    With CalendarFrame.OLEFormat.Object.ShapeRange
        .Fill.Visible = msoFalse
        .Line.ForeColor.SchemeColor = 12
        .Line.Weight = 2.5
    End With
    On Error Resume Next
    Set Calendar = ActiveSheet.OLEObjects(CalendarInputControlName)
    On Error GoTo 0
    If Not Calendar Is Nothing Then
        With Calendar
            .Left = Cell.Left + Cell.Width + 7 + HorizontalDelta
            .Top = Cell.Top + Cell.Height + 7 + VerticalDelta
        End With
    End If
    Calendar.LinkedCell = Cell.Address
    Calendar.Visible = False
    Calendar.Visible = True

End Sub

[End Code Segment]

Below is the code that is placed in the ThisWorkbook code module.

[Begin Code Segment]

Private Sub Workbook_Open()

    ResetCalendarInputControl

End Sub

[End Code Segment]

Obtaining the Control

Visit this website to get versions of the control installed prior to Excel 2007:

   http://www.fontstuff.com/mailbag/qvba01.htm

Instructions are provided on how to install the control.

Additional Information

The names of the calendar control (as listed in the ActiveX control chooser) installed with the versions of Office since Office 97 are listed below.

   2007: Calendar Control 2007
   2003: Calendar Control 11.0
   2002: Calendar Control 10.0
   2000: Calendar Control 9.0
   97: Calendar Control 8.0

All versions are registered with the same class name, "MSCAL.Calendar.7", which is used to load the control as illustrated below.

   Set Calendar = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", ...)

The file name of the control has also remained consistant: "MSCAL.OCX".

A final note about the technique used. When any ActiveX control is added or deleted from a worksheet the VBA environment is reset which means all global variables, module level variables, and static variables are cleared. To get around this deficiency, the calendar control is deleted and added when the workbook is opened and then hidden and shown from that point forward. The reason it needs to be deleted and added is that the version of the control can change from workstation to workstation.

Kevin

Thanks for the prompt reply. Could you please describe in greater detail, how to place the three code segments into module(s)? I have activated the developer toolbar in Excel 2007. Do I Insert > Module for each code segment or put all code in one module?

Thanks

To add VBA code to a regular module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Press CTRL+R to open the VBA project explorer. Select the menu command Insert->Class Module to create a new VBA class module. Paste the code into the document window that appears. Press F4 to open the properties window. Find the property (Name) and enter the name of the class (each class must be named because it's name is the only way by which it can be referenced.) Press ALT+F11 to return to the Excel workbook.

To add VBA code to a regular or general module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Select the menu command Insert->Module to create a new VBA module. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

To add VBA code to the ThisWorkbook module in an Excel workbook, press ALT+F11 to open the VBA development environment (VBE). Press CTRL+R to open the VBE project explorer. Find the module named ThisWorkbook and double-click it. Paste the code into the document window that appears. Press ALT+F11 to return to the Excel workbook.

Kevin

Kevin,
I folowed your instructions and plugged in the code. However, I'm receving the following error in the ResetCalendarInputControl() sub within Module1
"Compile Error:
Syntax Error"
 
If Err.Number <> 0 Err.Number <> 1004 Then is diplaying in RED.
I did not change any of the default object names: "Module1", "Class1", "ThisWorkBook"
Could the erro indicate that 'MSCAL.Calendar.7' isn't installed on my computer? If not, what do you think is going on?
 Thanks!
 

Public Sub ResetCalendarInputControl()
    Dim Calendar As Object
   
    On Error Resume Next
    ActiveSheet.OLEObjects(CalendarInputControlName).Delete
    Set Calendar = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", Left:=0, Top:=0, Width:=180, Height:=120)
    If Err.Number <> 0 Err.Number <> 1004 Then
       MsgBox "The Microsoft Calendar Control is not installed on this computer."
       HideCalendarInputControl
       Exit Sub
    End If
    Calendar.Name = CalendarInputControlName
    Calendar.Visible = False
End Sub

Fixed:

Public Sub ResetCalendarInputControl()

    Dim Calendar As Object
   
    On Error Resume Next
    ActiveSheet.OLEObjects(CalendarInputControlName).Delete
    Set Calendar = ActiveSheet.OLEObjects.Add(ClassType:="MSCAL.Calendar.7", Left:=0, Top:=0, Width:=180, Height:=120)
    If Err.Number <> 0 And Err.Number <> 1004 Then
       MsgBox "The Microsoft Calendar Control is not installed on this computer."
       HideCalendarInputControl
       Exit Sub
    End If
    Calendar.Name = CalendarInputControlName
    Calendar.Visible = False

End Sub

Kevin

Kevin,

The fix took care of the error I was getting but I don't see the Calendar control on the spreadsheet. I have several worksheets in the workbook. Would that affect the functionality?

These are the cells I need populated with the calendar control:

If Not Intersect(Target, [F2:F100]) Is Nothing And Target.Address = Target(1).MergeArea.Addres

Can you post your workbook?

Kevin

I tried attaching a .zip but I got a message that says .xlsm is not an extension the system will accept.

Is there another way to get this to you?

Change extension from .xlsm to .txt, zip, and upload.

Kevin

Here you go....

WorkBookTest.txt.zip

Kevin,

Works like a charm! Thanks for all your help, I appreciate it!!