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.
To insert the calendar ActiveX control into Excel, enable the Developer tab available on the Ribbon.
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, so you need to switch it on to see the tab on the Ribbon.
The Developer tab will appear on the Ribbon.
Inserting ActiveX Control
- In the Ribbon, select Developer > Controls > Insert > ActiveX Controls, and then select More Controls.
- Scroll down until you find the Microsoft Date and time Picker Control 6.0 (SP4).
- Select the 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 will be shown in Design Mode. This means that 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 select 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 select a date, the 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 will not 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.