How to add DatePicker calendar to cells in MS Excel

34
20




About this tutorial:

Video duration: 7:35
Follow the instructions here:

Important: Make sure that when you save the spreadsheet, you “Save As: Macro enabled workbook”

Need to install the DatePicker Extension? Go here:

34 COMMENTS

  1. I was able to create the date picker (calendar) and share with my team. All can use it except one. Is there a setting in Excel that would prevent someone from clicking on the cell? Currently he shows two lines where the date should be. There is no drop down.

  2. Can you just do it in one single cell? Not the entire column? Also, you make the drop down the entire cell? Like when you click on the cell, the calendar pops up? (like you do not need to necessarily click on the arrow) Thank you! This was one of the best videos to do this…and by one of, I mean THE BEST lol

  3. I have two computers. My work computer cannot have the file MScomctrl2.ocx installed or registered for security reasons. My personal laptop is a different story. I installed the ext as instructed and created the drop down date menu no problem. I saved the file and sent it to my work laptop. The function will not work. Does that file have to be installed on the computer opening the file?
    My office computer does have Active X abd Macros enabled, Thanks in advance

  4. I think it has something to do with this instruction because when I eliminate it the error does show up but then the toggles aren't lined up.    .Left = Target.Offset(0, 1).Left

  5. Sweet!!! Very well done! Thank you!
    oh… For some reason, I can't control the Format. At first it was just the date which was fine, but I wanted to make it yyyymmdd… that didn't work and now I get Date & Time no matter what I do…
    The DTPicker Properties don't seem to control the format like you would expect…
    Anybody know of a solution? Thanks

  6. Ok, So I think I have a fairly easy problem/question. I want to add the date picker to a particular column but start it on say row 4 of column J and add it to every row. I have changed the code to the following:

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    With Sheet1.DTPicker1
    .Height = 20
    .Width = 20
    If Not Intersect(Target, Range(ActiveSheet.Range("J4"), ActiveSheet.Range("J4").End(xlDown))) 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

    But when I do certain things in the worksheet, like say, highlight the entirety of my data (all columns and rows of data), it creates a run-time error '440': invalid property value error and the ".LinkedCell = Target.Address is highlighted yellow showing a target address of "$A$3:$N$10" which is essentially the limits of my data in this particular data set.

    Anyone have any idea on how I can fix my code to eliminate this issue. like I said, I just want to be able to add it to a particular column starting at a certain row (as my data has headers similar to the one shown at the very start of this video).

  7. First of all thank you for the this helpful guide.
    I was able to implement it, but when i select a horizontal row with the other non date columns i get the following error:
    Run-time error '1004: Application-defined or object-defined error
    When i debug i reach to the following line of code
    .Left = Target.Offset(0, 1).Left
    Any help is highly appreciated.
    Thanks

  8. hi.. really helpful but want to ask how can i link the date with another worksheet that once i write something in different worksheets then another sheet will automatically capture the date i write.. in summary i want to know how to make automatically date capture based on what i write in another worksheet or sheet.

  9. Simply CANNOT get the time to stop showing up next to the Date. Followed directions and all worked perfect. But when I saved and then reopened file, the drop downs always insert the time as well. Need help

  10. Thank you! This was very helpful –
    One thing that worked for me and may be helpful for others to clean up code with multiple ranges that require a date picker is you do not to re-write the entire code for every new range and add multiple date pickers.
    You can simply imply all ranges that require a date picker by changing the following  line of code: 
        If Not Intersect(Target, Range("A:A")) Is Nothing Then
    To add multiple ranges for this date picker function, just add a comma after A:A and specify new ranges separated with a comma, apply quotations only at the beginning and end of the list, for example, if you wanted Rows A, C, E and F to all have date pickers, simply adjust that single line of code as follows: 
        If Not Intersect(Target, Range("A:A, C:C, E:E, F,F")) Is Nothing Then
    Hope this might help people who need date pickers every other row to quantify status updates etc.

  11. Perfect. Easy to follow and well presented. Did the job just right but when I opened the spreadsheet up the following day it now displays the time as well as the date. Has anyone figured out how to fix this so it is date only.Thanks.

  12. Thanks for thé video. I had just one problem: thé case box don’t open in the cell. It’s static, ir don’t open on the select cell. How could i correct it?

  13. Good tutorial. Can you help on this: if you follow this tutorial to add date picker in the column A and if you afterwards select any whole row, you will get Run time 1004 error message. Why this is happening? How can I remove this message?Thanks

LEAVE A REPLY

Please enter your comment!
Please enter your name here