Excel Tips...

Put a Permanently Open Calendar on a Worksheet

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. Here's how...

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

Write the VBA Code

Now you need to write the code that will make the calendar work. Right-click on the calendar and choose View Code from the context menu. 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. Enter the following text (make sure you refer to the calendar by the name that Excel gave it):

ActiveCell.Value = Calendar1.Value

The code window should look like this:

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:

Range("B3").Value = Calendar1.Value

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. Click the Design Mode button on the Control Toolbox so that it is no longer depressed. 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 display the Control Toolbox, place Excel in Design Mode and proceed as before.

Other Options

Synchronise the Calendar

You can have the calendar synchronise 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 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:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(ActiveCell.Value) Then
        Calendar1.Value = ActiveCell.Value
    Else
        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...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Calendar1.Left = ActiveCell.Left + ActiveCell.Width
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)...

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If IsDate(ActiveCell.Value) Then
        With Calendar1
            .Visible = True
            .Left = ActiveCell.Left + ActiveCell.Width
            .Top = ActiveCell.Top
            .Value = ActiveCell.Value
        End With
    Else
        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

Hit Counter