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