How to add a DatePicker (Calendar) to Excel Cells


How to add a DatePicker (Calendar) to Excel Cells 1

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
Enable Developer Mode

[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
Name Date Picker
  • Right click on DateTime Control box
  • DTPicker Object
  • Properties
DTPicker Object > Properties
  • Enable CheckBox
Enable Checkbox
Excel Training Guide

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 Sub
  • Right click the DateTime Control box
  • View Code
View Code
  • Replace all of the existing code with the code from above
  • Close the Visual Basic editor
Paste Code
  • Deselect Design Mode
Deselect Design Mode
  • Click on any cell to remove DateTime Control box
  • Click on any cell in Column A to add a Date
Pick 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 Then

to

If Not Intersect(Target, Range("B:B")) Is Nothing Then

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 Then

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 Sub

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.

Adam Allard

Father of 2, husband of 1, developer of many. I'm a Software Engineer for Northrop Grumman creating web applications for the DoD. Currently I'm primarily working with Java and Angular based applications, although I have some years of experience in various languages and frameworks from previous jobs where I dabbled with Python & Django, C# & Xamarin, PHP, and Bootstrap. My hobbies include time with my family, wondering when the Green Bay Packers will win their next Super Bowl, drinking over-priced beer, and of course learning and teaching.

26 thoughts on “How to add a DatePicker (Calendar) to Excel Cells

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

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

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

  3. 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…

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

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

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

  5. 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?

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

  6. 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?

    1. Hey Tony, just figured it out. When you click properties with VBA opened, under the CheckBox, select from the dropdown ‘False’ to ‘True’

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

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

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

Leave a Reply

Your email address will not be published. Required fields are marked *

Recent Content