Martin Green's Excel Tips
|   Home   |   Excel   |   Access   |   Word   |   Outlook   |   VBA   |   eBooks   |   Handouts   |   Site Index   |

 

A Calendar for Excel...

Put a Permanently Open Calendar on a Worksheet

Published: 16 January 2006
Revised: 27 April 2013
Author: Martin Green
Screenshots: Excel 2003, Windows XP; Excel 2007, Windows 7
For Excel Version: 2003, 2007

One of my most popular tutorials shows you how to build A Pop-up Calendar for Excel. It involves building a VBA UserForm and placing an ActiveX Calendar Control on it. The user right-clicks on any cell and chooses InsertDate from the menu and the calendar appears. Choosing a date from the calendar places that date into the cell.

But you can take a simpler route and have a calendar control embedded into the worksheet itself so that it is permanently visible. This requires very little code and is very easy to do. The procedure differs slightly between Excel 2003 and Excel 2007. Both methods are described here (the VBA code is the same for both versions).

NOTE: The ActiveX Calendar Control used in this tutorial was discontinued by Microsoft from Excel 2010 so I have created an alternative solution for Excel 2007 and 2010. If you have Excel 2010, or just want to try something different take a look at my other tutorial: Put a Date Picker Calendar on an Excel Worksheet.

In Excel 2003

Display the Control Toolbox

Display the Control Toolbox by choosing View > Toolbars > Control Toolbox. Make sure that the Design Mode button (the first one on the Control Toolbox) is pressed in....

The Control Box toolbar

Click the More Controls button (the last one on the Control Toolbox) to display a list of available ActiveX controls and scroll down until you find the Calendar Control. It is marked with a number denoting your current version of Microsoft Office. The illustration below shows Calendar Control 11.0 indicating that it is the control version that came with Microsoft Office 11 (Office 2003). It doesn't matter which version you use. If you don't see the Calendar Control in the list follow my instructions in I Don't Have a Copy of mscal.ocx Calendar Control to obtain it.

Select the Calendar Control from the More Tools menu

 Place a Calendar on the Worksheet

Click on the Calendar Control entry in the list then click on the worksheet approximately where you want to place the calendar. When you do this the calendar control will appear and you can move and resize it in the same way you would any drawing object...

Place the Calendar Control on the worksheet

NOTE: Excel has given the calendar a name (Calendar1). If you want to change the appearance of the calendar (e.g. its colour, font or style) right-click on it and choose Calendar Object > Properties and make your choices from the dialog box.

In Excel 2007

Locate the Calendar Control

The tool you require is located on the Developer tab so, if it isn't already visible, you need to show it on the Ribbon. Click the Office Button and choose Excel Options. In the Popular section of the Excel Options dialog check the box marked Show Developer tab in the Ribbon. Click OK to return to Excel.

In the Controls section of the Developer tab click the Insert button to display a selection of controls. Click the More Controls button to open the More Controls dialog.

Click the More Controls button

The More Controls dialog offers a list of additional controls that are available. Scroll down the list and find Calendar Control. Select it and click OK.

Select the Calendar Control

Place a Calendar on the Worksheet

Click on the Calendar Control entry in the list then click on the worksheet approximately where you want to place the calendar. When you do this the calendar control will appear and you can move and resize it in the same way you would any drawing object...

Place the Calendar Control on the worksheet

Write the VBA Code

Now you need to write the code that will make the calendar work:

  • In Excel 2003: Right-click on the calendar and choose View Code from the context menu.
  • In Excel 2007: Click the View Code button in the Controls section of the Developer tab.

This opens the Visual Basic Editor with the current worksheet's code module open and an event procedure ready for you to enter your code...

Excel creates an Event Procedure for the Calendar Control

Excel has created a "Click" event procedure and the code you type here will determine what happens when the user fires the Calendar Control's Click event by choosing a date. Let's assume that you want to put a date in the currently selected cell. Add a code statement as shown here (Listing 1) making sure you refer to the calendar by the name that Excel gave it.

Listing 1. A procedure to write the chosen date into the active cell

Private Sub Calendar1_Click()
    ActiveCell.Value = Me.Calendar1.Value
End Sub

If you want the calendar always to place its date into a particular cell you can specify that cell's address in the code like this (Listing 2), substituting the address of the required cell as appropriate.

Listing 2. A procedure to write the chosen date into a specific cell

Private Sub Calendar1_Click
    Range("B3").Value = Me.Calendar1.Value
End Sub

When you have finished writing your code you can close the Visual Basic Editor by clicking the Close button in the top-right corner of the window or by choosing File > Close and Return to Microsoft Excel.

Activate the Calendar

The calendar will not work until you take Excel out of Design Mode.

  • In Excel 2003: Click the Design Mode button on the Control Toolbox so that it is no longer depressed.
  • In Excel 2007: Click the Design Mode button on the Developer tab to deselect it.

The calendar is now ready to work. If at any time you want to make changes to the design of your calendar or the way it works simply place Excel in Design Mode and proceed as before.

Other Options

Synchronise the Calendar

You can have the calendar synchronize with the currently selected cell so that, if that cell contains a date, the calendar will show the same date. The code below uses a VBA If Statement to do this. If it does not find a date in the selected cell it makes the calendar display the current date.

Return to the same code window as before. At the top there are two drop-down lists. Open the left-hand list (it currently shows the name of the calendar) and choose Worksheet. The Visual Basic Editor should write an empty event procedure for the SelectionChange event. If it displays something else choose SelectionChange from the right-hand list. Then enter code as follows (Listing 3).

Listing 3. A procedure to synchronize the calendar with the active cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(ActiveCell.Value) Then
        Me.Calendar1.Value = ActiveCell.Value
    Else
        Me.Calendar1.Value = Date
    End If
End Sub

Multiple Calendars

You can add as many calendars as you want. Each will have its own name and a separate event procedure and code (each referring to the relevant calendar by name).

Keeping the Calendar on View

The easiest way to keep the calendar permanently on view when scrolling down the worksheet is to use Excel's Freeze Panes command. Select a cell in Column A a little further down the worksheet than the bottom of the calendar and choose Window > Freeze Panes. Now only the part of the worksheet below that cell will scroll.

But if you want to get really clever with your calendar, you can have it move each time you select a different cell (Listing 4).

Listing 4. A procedure to move the calendar to the selected cell

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Me.Calendar1.Left = ActiveCell.Left + ActiveCell.Width
    Me.Calendar1.Top = ActiveCell.Top
End Sub

Hiding the Calendar

You might want to hide the calendar when the selected cell doesn't contain a date (I have combined the code for hiding, moving and synchronising the calendar here) (Listing 5).

Listing 5. A procedure to show or hide the calendar

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(ActiveCell.Value) Then
        With Me.Calendar1
            .Visible = True
            .Left = ActiveCell.Left + ActiveCell.Width
            .Top = ActiveCell.Top
            .Value = ActiveCell.Value
        End With
    Else
        Me.Calendar1.Visible = False
    End If
End Sub

Important Note

Remember that, if you distribute the workbook containing the Calendar Control to other users, the calendar will not be displayed unless their computer has the ActiveX Calendar Control installed. The ActiveX Calendar Control is normally installed with Microsoft Access, or with a full install of Microsoft Office Professional. If your computer does not have a copy of the Calendar Control read I Don't Have a Copy of mscal.ocx Calendar Control to find out how to get one.

    ^ top
    2003, 2013 Martin Green martin@fontstuff.com
tml> martin@fontstuff.com martin@fontstuff.com