Durjoy Paul
In Microsoft Excel, it’s effortless to insert drop down calendar. From the drop down calendar, you can easily insert any date. You can also create a link to the cell with drop down calendar where your defined date will be visible. In this article, we give a valuable overview of how to insert a drop down calendar in Excel. I hope you’ll find it fairly easy to use.
Download Practice Workbook
Download this Practice workbook
Step-by-Step Procedure to Insert Drop Down Calendar in Excel
To insert drop down calendar in Excel, we’ll show you how to do it step-by-step. All of the following steps are really easy to use and it enhances your excel knowledge to apply it for a bigger purpose.
Step 1: Enable Developer Tab in the Ribbon
Before doing anything on inserting drop down calendar in Excel, you need to show the Developer tab on the ribbon. As an Excel user, when you open your Excel workbook, primarily, there is no Developer tab on the ribbon. So, you need to change it by customizing the ribbon.
Steps
- Primarily, click on the File tab. In the File tab, select the Options.
- That will open up the Excel Options dialog box. Select Customize Ribbon.
- Now, in the right corner, there is Customize the Ribbon, select Main Tabs from there, and in the Main Tabs, click on the Developer option box. After that, click on ‘OK. This will open up the Developer tab on the ribbon.
Read More: How to Insert Day and Date in Excel (3 Ways)
Step 2: Insert Drop Down Calendar
To insert drop down calendar, you have to follow the following steps.
Steps
- First, select the Developer tab. From the Controls group, select the Insert option.
- Now, in the Insert option, click on More Controls from ActiveX Controls.
- A More Controls dialog box will appear, select Microsoft Date and Time Picker Control 6.0 (SP4). Click on ‘OK’.
- Now, click on any cell where you want to put this.
- After inserting the drop down calendar, you’ll see an EMBEDDED formula in the formula bar.
Read More: How to Insert a Date Picker in Excel (With Step-by-Step Procedure)
Step 3: Customize Drop Down Calendar
After inserting your drop down calendar in your preferred cell, you can customize this drop down calendar.
Steps
- You can customize your drop down calendar by simple drag it.
- You can alter the Properties of your drop down calendar by right-clicking on the drop down calendar. But remember, to do this you have to keep Design Mode on.
- In the Properties dialog box, you can change the height, width, and some more things.
- You can put the drop down calendar into any place by just dragging it to that place.
Read More: How to Insert Date in Excel (7 Simple Methods)
Similar Readings
- How to Combine Date and Time in One Cell in Excel (4 Methods)
- Change Dates Automatically Using Formula in Excel
- How to auto populate date in Excel when cell is updated
Step 4: Link Drop Down Calendar to a Cell in Excel
In the Properties of drop down calendar, you may see there is a ‘LinkedCell’ option. Excel cannot read any date from your drop down calendar, so, you need to link it to a cell.
Steps
- From the Developer tab, turn on the Design Mode.
- Now, right-click on the drop down calendar, and from the Context Menu, select Properties.
- In the Properties dialog box, put any cell number in the LinkedCell option.
- Now, turn the Design Mode off and select any date in the drop down calendar, it will appear at that referred cell.
- A warning dialog box may appear. Click on ‘OK’.
- Change the Checkbox value from False to True in the Properties dialog box to accept null values.
- If you want to see the VBA code associated with this, just right-click on the drop down calendar and select View Code.
Read More: Excel Automatically Enter Date When Data Entered (7 Easy Methods)
Insert Drop Down Calendar in Entire Column
Another interesting thing can be done by using drop down calendar. You can insert drop down calendar in the entire column or multiple columns. Whenever you click any cell, a calendar will open up and you can select a date from there. Both of these things can be done particularly by VBA codes.
1. Drop Down Calendar for a Single Column
Steps
- As we want to insert drop down calendar for a single column. First, insert drop down calendar by using the above steps.
- Now, right-click on drop down calendar and select View Codes.
- When you open the view code option, a visual basic interface will appear and there are random codes in that sheet. If you have to customize it, so delete this and copy the following code and paste it there.
Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet3.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("B5:B7")) 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- Now, turn off the Design Mode.
- Select any cell within the given cells in the VBA code, you will find drop down calendar in every cell within the cell limit.
Explanation of the VBA code:
With Sheet3.DTPicker1
.Height = 20
.Width = 20This code denotes that you need to select your sheet name where you want to apply this code and the date picker number. You can also modify the height and width values.
If Not Intersect(Target, Range("B5:B7")) Is Nothing Then
.Visible = TrueThis code denotes that if you select any cell in this range, drop down calendar will be visible in every cell within this range.
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.AddressTop property denotes the top belongings value of a specified cell.
Left property denotes the next right cell of the specified cell.
LinkedCell connects drop down calendar with the specified cell.
Else
.Visible = FalseThis denotes if you select any other cell than this given cell, drop down calendar won’t be visible.
Read More: How to Insert Dates in Excel Automatically (3 Simple Tricks)
2. Drop Down Calendar for Multiple Columns
If you want to use your drop down calendar for multiple columns, we can follow these steps. Remember, you have to insert multiple drop down calendars for doing this.
Steps
- Insert multiple drop down calendars from the Developer tab.
- Place these drop down calendars to your desired position by dragging.
- We would like to insert drop down calendar in column B and column D. Right-click on the drop down calendar and select View Code. Now, copy the following code and paste it into that sheet.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet5.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("B5:B9")) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With
With Sheet5.DTPicker2
.Height = 20
.Width = 20
If Not Intersect(Target, Range("D5:D9")) 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- That will create two drop down calendars in column B and column D in a given range. You can put any date from drop down calendar in that range.
Note:
You have to change CheckBox from False to True to avoid any error message.
Read More: Excel Macro: Insert Date and Time in a Cell (4 Examples)
Problem with Drop Down Calendar
If you are an active user of Microsoft 365 or Microsoft Excel 2019, you won’t find the drop down calendar. This drop down calendar is only available in Microsoft Excel 2007 and 32-bit versions of Excel 2010,2013 and 2016.
Conclusion
We have discussed the step-by-step process to insert drop down calendar in Excel. We have also tried to show how to use a drop down calendar both for the single column and multiple columns. I hope you can gain a lot of knowledge from this article. If you have any questions, feel free to ask in the comment section, and don’t forget to visit our Exceldemy page for more knowledge.
