How to Insert Date Picker in Excel?
To insert Drop Down Calendar, we will use an ActiveX Control, which is ‘Microsoft Date and Time Picker Control 6.0 (SP6)’.
Suppose we have to maintain data for employees of a company. There are several fields like
- Emp Code
- Emp Name
- Emp Joining Date
- Emp Department
To enter data in MS Excel, we have created the following format.
To enter Emp Joining Date, we need to create a drop-down calendar so that it would be easier for the user to enter joining dates.
To create a drop-down calendar, steps are given below –
4.9 (1,353 ratings) 35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
We need to insert an ‘ActiveX control’ named ‘Microsoft Date and Time Picker Control 6.0 (SP6)’. To insert, we will use the ‘Insert’ command under the ‘Controls’ group in the ‘Developer.’
If the ‘Developer’ tab is not visible, below are the steps to follow to make the same visible.
- Step 1: Under the ‘File’ menu, choose ‘Options’
- Step 2:A dialog box named ‘Excel Options’ will open. Choose ‘Customize Ribbon’ from the left edge of the dialog box. Checkbox for the ‘Developer’ tab and click on ‘OK.’
- Step 3:Now, we can see the ‘Developer’ tab at the last of the ribbon.
- Step 4: Choose ‘More Controls’ from ActiveX Controls.
- Step 5: Choose ‘Microsoft Date and Time Picker Control 6.0 (SP6)’ from the list and click on ‘OK.’
- Step 6: Click anywhere on the worksheet to create the drop-down calendar.
- Step 7: Right-click on ‘Date Picker’ and choose ‘Properties’ from the list.
- Step 8: Change the value from ‘False’ to ‘True’ for ‘CheckBox’ property so that null values can also be accepted. Close the ‘Properties’ dialog box.
- Step 9: Right-click on Date Picker again and choose ‘View Code’ from the contextual menu.
- Step 10: In ‘Visual Basic Editor,’ we can see that some code is already written. Replace the code with the following code.
Private Sub Worksheet_SelectionChange(ByVal Target As Range) With Sheet1.DTPicker1 .Height = 20 .Width = 20 If Not Intersect(Target, Range("C:C")) Is Nothing Then .Visible = True .Top = Target.Top .Left = Target.Offset(0, 1).Left .LinkedCell = Target.Address Else .Visible = False End If End With End Sub
- Step 11: The first statement in the code tells the MS Excel Compiler to run the code whenever a new cell is selected (Selection is changed). The selected cell is sent to the Sub procedure as ‘Target.’
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
- Step 12: These statements set the height and width of the Date Picker to 20 points in excel. It can be observed that we have used the ‘With’ operator so that we do not need to refer to DTPicker1 again and again.
With Sheet1.DTPicker1 .Height = 20 .Width = 20
- Step 13: The following ‘If’ clause sets the criteria that if any cell is selected in the ‘C’ column, only then Date Picker gets visibility. We have used the ‘Intersect’ function as this function checks whether we have selected any cell in the C column, then this function will return the address; otherwise, the value would be null.
If Not Intersect(Target, Range("C:C")) Is Nothing Then .Visible = True
- Step 14: ‘Top’ property of Date Picker is set as equal to the ‘Top’ property value of the Selected cell. It means that this will go along with the upper border of the selected cell.
.Top = Target.Top
- Step 15: This statement sets the left property of the Date Picker as equal to that of the next right cell of the selected cell (Distance of left border of the D column from the extreme left of the worksheet). To get the reference of the next right cell, we have used the ‘Offset’ function with 0 as row argument and 1 as column argument as this will get a reference to the cell in the next column.
.Left = Target.Offset(0, 1).Left
- Step 16: This statement links the Date Picker with the Target cell so that whichever value is selected in the drop-down calendar that is displayed in the cell.
.LinkedCell = Target.Address
- Step 17: The ‘Else’ statement tells the compiler to not display the Date Picker when any cell except that in the C column is selected.
Else .Visible = False
- Step 18: In the end, we close the ‘If.’
- Step 19: At last, we need to close ‘With.’
- Step 20: Now, Sub Procedure will end.
Make sure we save the file with the ‘.xlsm’ extension as it saves the VBA code we have written, and we will be able to run this code when any cell is selected in the C column.
Now, whenever we select any cell in the ‘C’ column, we can see a drop-down calendar at the top right corner of the selected cell. We can open the drop-down calendar by clicking on the down arrow symbol.
We need to click on any date in the calendar in the selected month to enter that date in the selected cell.
We can change month to previous or next using the arrow button placed at the left and right side of the calendar.
We can also click on the month to choose a month from the drop-down.
We can also change year by clicking on year and then using up and down arrows to choose the required one.
Things to Remember
- ‘Microsoft Date and Time Picker Control 6.0 (SP6)’ is not available for the 64-bit version of MS Excel.
- After writing the VBA code, we need to save the file with ‘.xlsm’ (Excel Macro-Enabled Workbook) extension; otherwise, the VBA code would not run.
This has been a guide to an excel date picker. Here we discuss how to insert date picker in excel along with practical examples and a downloadable excel template. You may learn more about excel from the following articles –