See all How-To Articles

Insert Drop-Down Calendar With Date Picker Control in Excel

This tutorial demonstrates how to insert a drop-down calendar with the date picker control in Excel.

 

date select date

 

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

  1. Click on the File tab in the Ribbon and go down to Options.
  2. 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.

 

vba vbe developer

 

  1. Click OK.

The Developer tab now appears on the Ribbon.

Insert ActiveX Control

  1. In the Ribbon, select Developer > Controls > Insert > ActiveX Controls, and then select More Controls.

 

date more controls

 

  1. Scroll down until you find the Microsoft Date and Time Picker Control 6.0 (SP4).

 

date select activex

 

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

 

date designmode

 

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.

 

date control handles

 

You can also right-click on the control and select Properties to customize the control.

  1. 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, 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.

 

date design properties

 

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

 

date-control switch off design

 

  1. Once you have switched off design mode, you can use the Date and Time Picker.

 

date control select

 

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.