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

 

A Date Picker for Excel...

Put a Date Picker Calendar on an Excel Worksheet

Published:29 April 2013
Author: Martin Green
Screenshots: Excel 2010, Windows 7
For Excel Versions: 2007, 2010

About This Tutorial

This tutorial shows you how to add a calendar an Excel 2007 or 2010 worksheet using an ActiveX control. I wrote it as an alternative to my previous tutorial for Excel 2003 and 2007, Put a Permanently Open Calendar on a Worksheet, because the ActiveX control it used was dropped from version 2010 of the program. In the tutorial you place a calendar/date picker on a worksheet and link it to a specific cell. Although not very flexible it fulfils a purpose and has the advantage that, providing you want the calendar linked to a specific cell, no VBA programming is necessary. If you need it to be more flexible, with a simple line of code you can have it enter a date into any selected cell. If you want to create a really flexible and powerful pop-up calendar tool take a look at my VBA tutorial, A Pop-up Calendar for Excel.

Place the ActiveX Control on the Worksheet

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:

  • In Excel 2007 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 Excel 2010 click the File tab, choose Options and in the Excel Options dialog choose Customize Ribbon. In the Main Tabs section check the box marked Developer then 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 Microsoft Date and Time Picker Control. The accompanying number will differ according to which version of Microsoft Excel you are using. Select it and click OK.

Select the Microsoft Date and Time Picker Control

Click on the worksheet approximately where you want the control to appear then use the mouse to resize and position it as you would any drawing object.

Place the ActiveX control on the worksheet

Note that the control is represented by an expression in the Formula Bar and its name is shown in the Name Box.

Link the Control to a Specific Cell

Define the Control's Properties

Before the Date and Time Picker Control can be used its properties must be defined. With the control selected, in the Controls section of the Developer tab click the Properties button. This opens the Properties window listing all the properties of the Date and Time Picker control. Here you can specify a format for the date, define a range of allowed dates as well as  many other useful properties.

If you want to link the calendar to a specific cell so that, when a date is chosen it is entered into that cell only, you must enter the address of the cell into the LinkedCell property. In this example I have specified cell A1.

Enter the address of the linked cell in the Properties window

Activate the Control

The calendar needs to be activated before it will work. Click the Design Mode button on the Developer tab to take the worksheet out of design mode. Job done! To insert a date click the control's down-arrow to open the calendar. When you select a date the calendar will close and the date will be written into the specified cell.

Select a date to enter it into the specified cell

If you need to change anything simply put the worksheet back into design mode by clicking the Design Mode button. This will allow you to select any of the controls on the worksheet and will activate the appropriate tools on the Developer tab.

Link the Control to the Active Cell

If you want the chosen date to be entered into the currently selected (active) cell, a little VBA code is required. Do not enter a cell address into the LinkedCell property of the control's property sheet. Instead, click the View Code button in the Controls section of the Developer tab. This opens the current worksheet's code module in the Visual Basic Editor.

At the top of the code window there are two drop-down lists, one marked (General) and the other marked (Declarations). Open the General list where you will see the name of the Date Picker control you placed on the worksheet (e.g. DTPicker21). Choose it. Excel automatically creates an empty event procedure. This isn't the one you want and you can delete it later but leave it there for now. Open the Declarations list and choose Change. This creates an empty procedure for the control's Change event. Any code you enter here will run automatically when the date in the Date Picker changes. (Now you can delete the one you didn't need.) Enter the code statement shown below (Listing 1).

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

Private Sub DTPicker21_Change()
    ActiveCell.Value = Me.DTPicker21.Value
End Sub

Open the Debug menu and choose Compile... to check your code for errors and correct anything necessary then Save your work. You can close the Visual Basic Editor and return to Excel. Click the Design Mode button on the Developer tab to take the worksheet out of design mode. Job done! Test your calendar by selecting a cell then choosing a date from the Date Picker.

Link the Control to All the Selected Cells

If you want the chosen date to be entered into all the currently selected cells, create a Change event procedure as described in the previous section and enter code as shown below (Listing 2).

Listing 2. A procedure to write the selected date into all the currently selected cells

Private Sub DTPicker21_Change()
    
Dim cell As Object
    
For Each cell In Selection.Cells
        cell.Value = Me.DTPicker21.Value
    
Next cell
End Sub

Check, Compile and Save your code then return to Excel and click the Design Mode button on the Developer tab to take the worksheet out of design mode. Job done! Test your work by selecting a range of cells, either a rectangular block of cells or a non-contiguous multiple selection (click on one cell then [CTRL]+Click on other cells to select them as well).

    ^ top
    2013 Martin Green martin@fontstuff.com
td>     2013 Martin Green martin@fontstuff.com