Excel Date Picker

Philip Treacy

July 9, 2015

In my previous post about Excel forms the date picker I used was a 32 bit ActiveX control. If mentioning 32 bit ActiveX controls makes your eyes roll back in your head, then this post is for you 🙂

This date picker is implemented as a VBA class. Which means all you need to do is include that class module in your workbook and you are ready to use the date picker in your forms. I've even included an example workbook with all the code you need.

excel date picker example

Related Articles: I've written other related articles on Excel Forms which might interest you.

Excel Forms Create a simple user form

Excel Form Data Validation How to check the data being entered in your form is correct, and communicating with the user about errors

Excel Forms : Insert, Update and Delete Data Using your Excel Form to insert, update and delete records in a table.

Searching for Data With a User Form. Create a userform to search for data on the worksheet.


Because the code for the date picker is included in your workbook, you can distribute it and anyone using your workbook doesn't have to install any other files.

The ActiveX control only ran on 32 bit systems, but this date picker class runs on any version of Excel both 32 and 64 bit.

Excel date picker code

My thanks for the class code go to the authors, Krisztina Szabó, Gábor Madács and Roberto Mensa over at https://sites.google.com/site/e90e50/calendar-control-class. They do some great stuff over there so have a look.

You can download the example workbooks from E90E50 which let you insert a date into a cell in your sheet. My sample code presents a form and allows you to choose a date. What you do with that date is up to you, so it's ready for you to adapt to your needs.

My sample workbook also contains code to reset the date to today.

Download Your Excel Date Picker

Enter your email address below to download the sample workbook.

By submitting your email address you agree that we can email you our Excel newsletter.

Microsoft Power BI Community Super User Logo

AUTHOR Philip Treacy Co-Founder / Owner at My Online Training Hub

Systems Engineer with 30+ years working for companies like Credit Suisse and E.D.S. in roles as varied as Network & Server Support, Team Leader and Consultant Project Manager.

These days Philip does a lot of programming in VBA for Excel, as well as PHP, JavaScript and HTML/CSS for web development.

He's particularly keen on Power Query where he writes a lot of M code.

When not writing blog posts or programming for My Online Training Hub, Philip can be found answering questions on the Microsoft Power BI Community forums where he is a Super User.

Excel Forms

Excel Form Data Validation

38 thoughts on “Excel Date Picker”

  1. Hi Phil,

    Great tool, absolutely love it!

    EXCEPT that the output appears as an American date (in the userform’s textbox that I call it from); Can this be made to appear as “ddmmmyy” format to avoid confusion?

    jim

    Reply

    • Hi Jim,

      Sorry, VBA always handles dates in the US format. You may have to use the FORMAT function to format your date as you wish.

      If you want to start a topic on the forum and supply your code I can take a look at it.

      Regards

      Phil

      Reply

  2. Hi there,

    This Date Picker is fantastic. I have been looking for something like this as a VBA class for a very long time as I have made a complete management workbook for controlling inventory and jobs. We use this over a network and as a shared workbook, therefore there is always issues with compatibility. I can see there’s a lot of work that went into building it.
    Thank You so much for the time and effort. Outstanding job done for everyone involved. Really Amazing!!

    Reply

  3. Dear Authors,

    I find this solution really good. I am an Amateur VBA user.
    How could I incorporate this calendar in another userform, like a pop-up calender?

    How should I connect it to the TextBox?

    Thank you for your answer.
    Kind regards
    Szabolcs

    Reply

    • Hi Szabolcs,
      All you need to do is include that class module in your workbook and you are ready to use the date picker in your forms.
      That form has a Show Date button, the code behind that button is:
      Private Sub ShowDate_Click()
      MsgBox Calendar1.Value
      End Sub
      Instead of displaying a message, you can send that date anywhere in your workbook, or in another userform.
      If you have a textbox in your form, use the Click event of that textbox to display the calendar when user clicks on that textbox, then save the date to that textbox:
      Private Sub TextBox1_Click()
      DatePickerForm.Show
      End Sub
      Then, the Show Date button can be renamed to Save, and the code should be:
      Private Sub ShowDate_Click()
      TextBox1.Value = Calendar1.Value
      DatePickerForm.Hide
      End Sub

      Reply

      • Hi Catalin,

        I’ve tried your solution, but I can’t seem to get the calendar to pop up when clicking into the textbox itself. When I double click the textbox, the code is TextBox1_Change().

        I then tried to add a command button to open up the calendar, but I can’t seem to send the value to the textbox. It says that I have not defined TextBox1.Value.

        Any ideas on this?

        Thanks in advance!

        Reply

        • Hi Gabriel,
          In a standard vba module, declare the CValue parameter:
          Public CValue As Date
          Change from TextBox1_Change() to TextBox1_Enter() and use this code:
          Private Sub TextBox1_Enter()
          DatePickerForm.Show
          TextBox1.Value = CValue
          End Sub

          Reply

          • Hi Catalin,

            Thanks for the reply!
            Now the calendar does pop up when I go into the textbox, however, the date doesn’t get entered. It shows as 12:00:00 AM.

            To be clear, do I still do the following?:
            “Then, the Show Date button can be renamed to Save, and the code should be:
            Private Sub ShowDate_Click()
            TextBox1.Value = Calendar1.Value
            DatePickerForm.Hide
            End Sub”

            I tried deleting it, so that I would just close the form after I have picked the date I wanted, but still no go. Having the sub or not doesn’t seem to make a difference, I still get the timing.

          • Do you have that public parameter set in a standard module?
            Basically, you should select a date and simply close the form.
            You will have to upload your sample file so I cn see what’s wrong, use our forum to create your own topic and upload.

      • Hi author of this great feature,

        Im preparing for my coleagues an excel form to make thei data entry easier.
        How ever im now fighting with the Calnedar Form.

        I have in my UserForm a textfield and I want after click to this textfield the CalendarPickForm to pop up and save the selected date to this textfield, but after pasting this code to the ShowDate button in the CalendarForm I get the error that the variable is not defined, I think the error is refering to the TextBox1 name, what Im making wrong?

        Private Sub ShowDate_Click()
        TextBox1.Value = Calendar1.Value
        DatePickerForm.Hide

        Another question is how to proceed when I want to have in my form 2 textfield to you could pick up a date?
        Lets say 1 field is for Order Date and the second one for the Shipping date?

        Thank you in advance!
        What a great work you make 🙂

        Reply

  4. Good evening Phil,
    I have enjoyed a few Excel samples and (free) seminars. Impressive!.
    I am not the youngest around with my 72 years, but I like Excel very much and still try to understand and learn in te meantime all the values and possibilities of Excel 2016.

    Regarding the Date picker: the reset function in the userform is not working correctly and creates an error of missing library or object. The Date functions is the one that creates the issue. It does not matter where I use the Date function, even a simple range (“D5”). value = Date does not work. It does however in a new worksheet.

    Do you know what is going on here, as I simply do not understand why it is not working

    Rgds
    Wietze Plantinga

    Reply

    • Hi Wietze,
      Nice to hear you’re still fighting with excel, there is always something to learn.
      From VB Editor window, go to Tools->References, there seems to be a broken reference there: missing microsoft common controls 2. Uncheck that reference and it should work.
      Regards,
      Catalin

      Reply

      • Thanks Catalin, your response is fast!
        It took me a while, but found the missing microsoft controls 2 and removed it.
        As you predicted: it works fine now!
        Again: thank you very much!
        Regards
        Wietze Plantinga

        Reply

    • I also found the same error after finding this awesome website. I found a simple fix for the ‘reset date’ command button error. It is replace “date” with vba.date.

      Private Sub ResetDate_Click()
      Calendar1.Value = VBA.Date
      End Sub

      Reply

  5. Your Excel ‘Date-Picker’ is phenomenal…as a programmer, you are exceptionally gifted! Thank you for sharing this Tool…and allowing us to incorporate this into our UserForm! Appreciate everything, as always!

    Reply

  6. This used to work on Windows 10 (64 bit) and Office 2016 (64 bit) but after a recent Windows update it no longer works – day numbers are not showing and you cannot click the days with the mouse – only the arrow keys!

    Reply

  8. Cannot find the Enter date in Cell after double clicking the DatePickerForm. Unable to get date to populate into the cell range I want.

    Thank You

    Reply

    • Thanks Randy.

      Once you select a date in the form, you can access it using 

      Calendar1.Value

      So if you wanted to put the date into A1, in your VBA you could write 

      Range("A1").Value = Calendar1.Value

      Cheers

      Phil

      Reply

      • Hi Phil,

        This macro is out of my league. Can you explain where in the VBA macro to place…
        Range(“A1”).Value = Calendar1.Value

        Thanks,

        Randy

        Reply

        • Hi Randy,

          No worries, I’ve added the code to this workbook

          If you open the VBA editor (ALT+F11), double click on DatePickerForm and then double click on the button ‘Enter Date in Cell’ you’ll see this

          Private Sub EnterDate_Click()

    Range("B6").Value = Calendar1.Value

End Sub

          which is entering the date (Calendar1.Value) into cell B6 (Range(“B6”).Value)

          Now if you run the form, click on ‘Enter Date in Cell’, B6 will contain the date you selected in the calendar.

          Cheers

          Phil

          Reply

          • My apologies in advanced, since I am a complete newbie, but I would recommend to substitute the code under Show Date button (perhaps change label for “Insert Date”) with the following:

            [code]
            Private Sub ShowDate_Click()

            ActiveCell.Value = Calendar1.Value
            DatePickerForm.Hide

            End Sub
            [/code]

            This will allow for a date to be entered into any selected cell, and will close the calendar at the same time.

            btw – Thanks for this! I’ve been looking for something I could customize to my needs and this is more than perfect.

  10. Your date object is just what I am looking for. But I need to have one calendar object to select a start date and paste the value into one cell and another calendar object to paste an end date into another cell. Do you recommend that I make a copy of the class module to get the second calendar?

    Thanks (I’ve taken your Dashboard course and I’m planning on taking your PowerPivot course soon).

    – Marteen

    Reply

    • Hi Marteen,

      You don’t need to copy the class, you need to create another instance of the calendar. In the UserForm_Initialize() you’ll see a block of code that begins 

      If Calendar1 Is Nothing Then

      Copy/paste this and in the pasted block change Calendar1 to Calendar2. You’ll also need to change the reference to the frame is loaded into to Frame2. You should end up with this

      If Calendar2 Is Nothing Then
        Set Calendar2 = New cCalendar
        With Calendar2
            .Add_Calendar_into_Frame Me.Frame2
            .UseDefaultBackColors = False
            .DayLength = 3
            .MonthLength = mlENShort
            .Height = 120
            .Width = 180
            .GridFont.Size = 7
            .DayFont.Size = 7
            .Refresh
        End With
    End If

      At the top of the form code module you will also need to add this line

      Private WithEvents Calendar2 As cCalendar

      and in the form itself, you need to create a new frame (or copy Frame1) and name this Frame2. This is where Calendar2 is loaded into.

      An alternative approach, and one that I think would look better for what you want, is to have a text box for both the Start Date and End Date, and when they are clicked on, load a separate form that has the calendar in it. The user can choose the date, and then close the calendar. You can then display the date(s) in the text boxes. This way you don’t have 2 calendars taking up a lot of space in the main form.

      Let me know if you get stuck.

      Regards

      Phil

      Reply

  11. Hi Phil

    I get a “Compile Error! Can’t find project or library” message when clicking the Reset Date button.

    I had a look at the code, looking for how the form is initially populated with the default date (today), but couldn’t find it.

    Cheers
    Col

    Reply

    • Hi Col,

      If you open a Helpdesk ticket and attach your workbook generating the error I’ll look at it for you.

      Regards

      Phil

      Reply

Leave a Comment

Current ye@r *