Do you need a DatePicker Calendar in your Excel spreadsheets? Follow this quick and simple tutorial to find out how. (If you prefer video, scroll down to the bottom of the page!)
Step 1. Enable Developer Mode in Excel
- File
- Options
- Customize Ribbon
- Ensure Developer checkbox is enabled
[sibwp_form id=100]
Step 2. Setup DateTime Control box
- Microsoft Date and Time Picker Control
- Click on random cell to place control box
- Edit or remember the name box of the DateTime Picker, in this case, we will leave it as DatePicker1
- Right click on DateTime Control box
- DTPicker Object
- Properties
- Enable CheckBox
Step 3. Copy the Visual Basic code
To assign column A as a DatePicker, copy the following code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet1.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("A:A")) 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 SubCode language: PHP (php)- Right click the DateTime Control box
- View Code
- Replace all of the existing code with the code from above
- Close the Visual Basic editor
- Deselect Design Mode
- Click on any cell to remove DateTime Control box
- Click on any cell in Column A to add a Date
Congratulations!
Important: Make sure that when you save the spreadsheet, you “Save As: Macro enabled workbook”
How to have multiple columns with DatePickers
The above steps only work for single columns, or columns that are right next to each other. If you wanted the DatePicker column to be in column B instead, then you would change the line
If Not Intersect(Target, Range("A:A")) Is Nothing ThenCode language: JavaScript (javascript)to
If Not Intersect(Target, Range("B:B")) Is Nothing ThenCode language: JavaScript (javascript)Or if you wanted it from Column E to Column G, then you would change that line to
If Not Intersect(Target, Range("E:G")) Is Nothing ThenCode language: JavaScript (javascript)However, if you need a DatePicker in two or more non-adjacent columns, then you will need a separate DatePicker (each with a separate name in the Name Box) for each non-adjacenet group. For example, let’s say we need column A, B, E, F, and H to all be DatePickers. We would need to perform the above steps 3 separate times for 3 separate DatePickers.
- 1 for columns A, B
- 1 for columns E, F
- 1 for column H
The steps will be very similar, but the code will be:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Sheet1.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range("A:B")) 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 Sheet1.DTPicker2
.Height = 20
.Width = 20
If Not Intersect(Target, Range("E:F")) 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 Sheet1.DTPicker3
.Height = 20
.Width = 20
If Not Intersect(Target, Range("H:H")) 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 SubCode language: PHP (php)Important legal notice: This post contains affiliate links. We are compensated for many of our product recommendations. Pragmatic Ways is a participant in the Amazon Associates affiliate program, as well as other affiliate programs. While we are always careful to only recommend the products we use and recommend, we want to be open and transparent about our relationship with companies we recommend.
Why not use one line for columns?
If Not Intersect(Target, Range(“A:B,D:E”)) Is Nothing Then
Nice
Many thanks discussing this statement and making it public
Not able to save the file says-
The following cannot be saved in macro- free books
VB project
Thanks so much. I always wanted to do this in my documents and was happy to find this simple tutorial.
I’ve noticed something funky with the format of the date cell that this date picker produces which is giving me a problem using it in formulas. For example, if I have 2 date cells with the same date, say A1 has been manually entered with 24/11/2019 and cell B1 has the date entered using the date picker. If I then do a comparison, say something like =IF(A1=B1,”Success”,”Fail”) then the result produced is false and displays “Fail”. Any thoughts?
Have you tried “Formatting” the A1 cell to cast the values as a Date (in your specified format)
This may work, as then Excel would be treating the value as a Date instead of a String.
If that doesn’t work, then you may need to beef up your conditional check.
“A1=B1” is most likely checking a String value against a Date value, which would in fact result in False.
If the formatting trick doesn’t work, then you could try to cast the A1 as a Date in your IF conditional (or cast the B1 cell as a String in your IF conditional)
Both cells are formatted as dates. I even tried to use the Format Painter to make the formatting of A1 and B1 the same. It gets weirder though. If I do the comparisons is A1>B1, A1<B1 and A1B1 they all produce a false result. It’s as if the date picker field cannot be used in a formula. Not only that, the formatting is persistent. If I create a 3rd cell C1 and make it =B1, any comparisons with C1 produce the same results as above. Might have to give up on this one.
I really appreciate your help with my project!
Thank you! Clear and thorough – very helpful
What if these date cells are below a header cell – how do I modify the format so that it accounts for that.
Also will the sort function still work on these columns – It doesn’t appear to work for mine. Possibly user error…
I’m having the same problem, I want the calendar to start at A5 and then applied to each cell thereafter for the rest of the collumn. How would I go about starting it at A5 rather than the whole collumn?
I added this tool and every thing was perfect..but..when i added another code for “multiple selection from a drop down list” in the same sheet, the multiple selection doesn’t work.
I need some help plz
This happened to me too, did you ever figure it out?
I tried adding the calendar on a different sheet of the same project and it didn’t work. why?
Hi! Did you change the code to the sheet you were using? For instance, the code I have above was written for sheet 1 with this: “With Sheet1.DTPicker1”. If you were on sheet 2 instead, then you should change it to “With Sheet2.DTPicker1”.
Let me know if you already tried that or it doesn’t work!
Hello,
Thanks you!!
How can I make It not valid for the first two rows as they are table’s header?
I used an OFFSET operator. $F$3 is the first active cell, no other cells in the column are active unless data is entered into column A on the same row:
If Not Intersect(Target, Range(“=OFFSET($F$3,0,0,COUNTA($A$3:$A$1048576),1)”)) Is Nothing Then
Hi there – I tried using the calendar on a sheet and it works perfectly fine. However, the issue I am having is that after I add the calendar and VBA, I get a run time-error 1004 whenever I am trying to highlight a row (doesn’t happen on columns). It will still let me select the row but I have to cancel the error every single time which is a bit of a pain. Any ideas what the issue is?
I had the same issue. Added a small error handler to stop the dialog box:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo ErrorHandler
With Sheet1.DTPicker1
.Height = 20
.Width = 20
If Not Intersect(Target, Range(“A:A”)) Is Nothing Then
.Visible = True
.Top = Target.Top
.Left = Target.Offset(0, 1).Left
.LinkedCell = Target.Address
Else
.Visible = False
End If
End With
Exit Sub
ErrorHandler:
Exit Sub
End Sub
So I was able to follow all the instructions but when I right click the picker box in developer mode and get the DTpicker1 object and click properties. That properties box is empty. I cannot choose checkbox as there properties box is empty.
Any ideas?
Have the exact same issue..
Hey Tony, just figured it out. When you click properties with VBA opened, under the CheckBox, select from the dropdown ‘False’ to ‘True’
Does this code not work for macro-enabled workbooks?
Hi
I dont want the time on it how do i remove the time
Hi there,
Thanks for this great instructional video and page. It 95% worked for me. However, as with Paul Rose above, in one column that I placed the date picker, the time also comes up and it’s a fixed time, not current.
When I click at the top of the column this error message shows up:
“Run-time error ‘440’:
Invalid property value”
And when I click on “debug” it highlights this line in the code:
“.LinkedCell = Target.Address”
in the corresponding column of the date picker I’m having trouble with. I’ve been trying to find guidance on various other pages for this but haven’t come up with a fix for how to remove the time from that one column. I have 3 other date pickers in non-adjacent columns that work perfectly, that don’t have any time stamp that appears with the date.
Help please.
I’m also having the same problem with the time stamp. I want it off! I’ve tried everything, from basic Excel cell formatting of numbers to trying to manipulate the code of the Date Picker. If you’ve found a solution please write back. Thanks
Really cool!!! I have been looking for this for quite sometime. Just can’t figure out how to change the date format ie from dd/mm/yy to dd-Mmm-yy. Any help will be much appreciated
How can we remove the time stamp with the date? This is driving me bananas!
Putting the time to 00:00:00 “hides” it for me
To get rid of time I typed the date in. Copy the format to all cells then
Hey this is really neat! I got everything to work and saved my spreadsheet as a macro spreadsheet. The only issue I am having is once I try to reopen my spreadsheet all the DatePickers are broken. I get “compile error: Method or data member not found. Any ideas on how to fix this?
Thanks, Adam
yes! works perfect, except when you save and re-open the file it timestamps!
Just awesome! Thank you so much!
Does this work on 64-bit systems or does it have to be 32-bit?