How to Create a Drop-Down Calendar in Excel

By Nick Peers

Discover how you can insert a drop-down calendar into your Microsoft Excel spreadsheets by using an ActiveX control.

You can insert drop-down calendars in your Microsoft Excel spreadsheets by using ActiveX controls. You can pick the date from the drop-down menu, just like you would pick an item from a list. In fact, the drop-down calendar is similar to a drop-down list.

Step 1

Right-click one of the tabs at the top of the Excel window and select Customize the Ribbon from the menu to open the Excel Options window.

Step 2

Select the Developer tab and click Add to display it.

Select All Tabs from the Choose Commands From drop-down menu, select Developer from the Main Tabs section and click the Add button to display the Developer tab. Click OK to close the Excel Options window. You will now see a Developer tab in the main ribbon.

Step 3

Switch to the Developer tab and click More Controls in the Controls menu.

Select the Developer tab, click Insert in the Controls group and click the More Controls button -- it looks like tools -- from the ActiveX Controls section to display the More Controls dialog.

Step 4

Select the Microsoft Date and Time Picker Control from the More Controls dialog.

Select Microsoft Date and Time Picker Control 6.0 and click OK to enter Design mode. You can create controls and edit their properties in Design mode.

Step 5

Creating the ActiveX control in Excel.

Click and drag with the mouse on the spreadsheet to set the size of the control and then release the right mouse button to create the ActiveX object.

Step 6

Selecting Properties from the context menu.

Right-click the new ActiveX control and select Properties from the context menu to display the Properties toolbox. The control can be configured by tweaking the settings in the Properties dialog.

Step 7

View the Properties dialog.

You can change the size of the object by changing the Height and Width values. To change the format of the date, choose a different option from the Format box. Set the maximum and minimum dates by changing the MaxDate and MinDate properties. To change the value displayed by default in the calendar control, select a different option from the Value box.

You can insert the date into a specific cell automatically when you select the date using the drop-down calendar control. To do so, type the cell column letter followed by the row number -- B3, for example -- into the LinkedCell field.