My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

You are here: Home / Excel VBA / Excel Date Picker
excel date picker
by

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.
excel date picker

More Excel VBA Posts

automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Static variables in VBA
Variables normally cease to exist once your Sub or Function has ended. But Static Variables allow you to preserve values after your code has finished.
save chart as image
List all the charts in your workbook then select the ones you want to save as either PNG or JPG. Sample workbook and code to download
Clearing Downstream Dependent Data Validation Lists
Change one of your data validation lists and clear the values in the other data validation lists dependent on your first choice.
Excel Status Bar
Use the Excel Status Bar to send messages to your users and to show a progress bar for your VBA code
Progress Bar for Excel VBA
Create your own progress bar for VBA in Excel. Use it to show that your code is still running, and how long before it finishes.
error handling in vba
Understand how Excel VBA generates errors, how to control what Excel does when an error occurs, and how to write your own error handling routines.
Finding File Metadata Using FileSystemObject
Find file meta data like the creation date, last modified date and file size using Windows FileSystemObject in Excel VBA
Automatically Add Items to Data Validation List
Automatically Add Items to Data Validation List by typing in the new data. Then sort the source list for bonus points

More Userforms Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Hide, Delete, Sort Sheets with VBA in Excel
Using a form and some VBA, you can quickly hide, unhide, delete and sort sheets in your Excel workbooks.
add data to combo box drop down list in excel form
If the values in your Excel Form's drop down list aren't sufficient, allow the user to add their own, and save the new value in the list to use again.
Excel forms, insert, update, delete data
Insert, update and delete data in an Excel table with a user form.
excel form data validation
Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel userforms
Learn how to create Excel forms for tasks like data entry. Use form controls to gather data, then enter this data into a table on your worksheet.
Excel Form Controls
Add interactivity to your spreadsheets with user friendly form controls like option buttons, combo boxes, check boxes and scrollbars.

More Excel VBA Posts

Display All Matches from Search in Userform ListBox
Search a range for all partial and full matches of a string, and display matching records (entire rows) in a userform listbox. Sample code and userform.
animating excel charts
Use animation correctly to enhance the story your data is telling. Don't animate your chart just for some eye candy. Sample code and workbook to download.
dynamic data validation lists in userforms
Data validation lists using the same source that are dynamically modified to prevent the same choice being made in each list.
show report filter pages for power pivot pivottables
PivotTables created from Power Pivot can't use the 'Show Report Filter Pages' option. But this piece of VBA allows you to do just that.
charting real time data in excel
Receive data in real time and chart the data as it arrives. Can be used to chart things like stock prices or sensor readings. Sample code and workbook
select multiple items from drop down data validation list
Choose multiple items from a data validation (drop down) list and store them all in the same cell. Sample workbook with working VBA.
Excel Calendar (Date Picker) to Use in Worksheets and Userforms
Easy to use, highly customizable and multi-language. This date picker is implemented as a userform that is simple to integrate into your workbook.
automating and emailing pivot table reports
Automate the creation and distribution of pivot table reports with some VBA. Send reports to multiple email recipients using Outlook.
search for data with userform
Search a list of records (like a table) using a user form, and then populate the fields of the search form when the record is found.
Checking values in range objects with vba
Use built in tools or your own code to examine the values contained within Range objects in VBA. Sample code to download.
Category: Tag: ,

Reader Interactions

Comments

  1. jim

    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

    • Philip Treacy

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

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

    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

    • Catalin Bombea

      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

      • Gabriel Zeng

        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

        • Catalin Bombea

          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

          • Gabriel Zeng

            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.

          • Catalin Bombea

            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.

  4. Wietze Plantinga

    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

    • Catalin Bombea

      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

      • Wietze Plantinga

        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

  5. Catherine (Cathy) Diethorn

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

    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

    • Philip Treacy

      Hi Simon,

      Not sure why an update would break this. But then, MS updates have a history of doing such things 🙁

      Phil

      Reply

  8. Mike

    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

    • Catalin Bombea

      Hi Mike,
      Can you please upload your file on Help Desk? (create a new ticket) I cannot know what is wrong without seeing the file.
      Thanks for understanding.
      Catalin

      Reply

    • Philip Treacy

      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

      • Randy

        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

        • Philip Treacy

          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

          • EyeNoNothin

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

    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

    • Philip Treacy

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

    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

    • Philip Treacy

      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 Reply

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

Current ye@r *