This tutorial demonstrates how to insert a drop-down calendar with the date picker control in Excel.
There are numerous ActiveX controls available to be used in Excel, but they are not obviously available, and many users are not aware that they even exist.
Enable the Developer Ribbon
- Click on the File tab in the Ribbon and go down to Options.
- In the Customize Ribbon options, tick the Developer check box. The option is off by default. Switch it on to see the tab on the Ribbon.
- Click OK.
The Developer tab now appears on the Ribbon.
Insert ActiveX Control
- In the Ribbon, go to Developer > Controls > Insert > ActiveX Controls, and then click More Controls.
- Scroll down until you find the Microsoft Date and Time Picker Control 6.0 (SP4).
- Choose the date picker control, and then click OK.
Click and drag in your Excel sheet to size the date and time picker. Release the mouse to create the control.
The control is shown in Design Mode. This means you can drag the control to where you want it positioned, and you can a size the control using the handles on the control.
You can also right-click on the control and click Properties to customize the control.
- For example, you can set the LinkedData property to link the data selected to a particular cell – in this case, cell D2. When you pick a date, that date is stored in cell D2.
You can also set Print Object to false so that when you print the Excel sheet, the ActiveX control doesn’t show up in the printout.
- Close the Properties box when you have finished customizing the ActiveX control. Drag the control to the position in the worksheet where you want it to go, and then switch off design mode by clicking on the Design Mode button in the Controls group in the Developer tab on the Ribbon.
- Once you have switched off design mode, you can use the Date and Time Picker.
Note: Microsoft’s Date Picker control only works with 32-bit versions of Excel – not 64-bit. See this page to understand the differences between the 32-bit version and 64-bit version of Excel.