• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

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

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Excel Date Picker

You are here: Home / Excel VBA / Excel Date Picker
excel date picker
July 9, 2015 by Philip Treacy

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.
Please enter a valid email address.

Sample code is available here as an Excel .xlsm

excel date picker

More Excel VBA Posts

automating and emailing pivot table reports

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

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

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

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

Clear 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

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

Excel Progress Bar for 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

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

Finding File Meta Data 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

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

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

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

Multi-Language Excel Calendar (Date Picker) for 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

Searching for Data With a User Form

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

Hide, Delete and Sort Sheets With VBA Userform

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

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

Excel Forms – Insert, Update and Delete

Insert, update and delete data in an Excel table with a user form.
excel form data validation

Excel Form Data Validation

Thorough and effective Excel form data validation techniques. Clearly communicate errors to your users without annoying them.
excel userforms

Excel Forms

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

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

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

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

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

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

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

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

Multi-Language Excel Calendar (Date Picker) for 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

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

Searching for Data With a User Form

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

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: Excel VBATag: Excel VBA, Userforms
Previous Post:excel userformsExcel Forms
Next Post:Excel Form Data Validationexcel form data validation

Reader Interactions

Comments

  1. jim

    January 10, 2020 at 1:23 am

    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

      January 10, 2020 at 10:06 am

      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
      • jim

        January 10, 2020 at 7:18 pm

        Hi Phil

        using FORMAT to handle the output from the date form did the job perfectly thanks

        jim

        Reply
        • Philip Treacy

          January 11, 2020 at 9:25 am

          no worries, glad you got it sorted out.

          Regards

          Phil

          Reply
  2. Tiaan

    January 21, 2019 at 4:21 am

    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
    • Catalin Bombea

      January 21, 2019 at 4:20 pm

      Thank you Tiaan,
      Glad to hear you found it useful!

      Reply
  3. Szabolcs Kanyo

    March 22, 2018 at 10:03 pm

    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

      March 24, 2018 at 2:40 am

      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

        February 20, 2020 at 12:04 pm

        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

          February 20, 2020 at 8:06 pm

          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

            February 20, 2020 at 9:52 pm

            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

            February 21, 2020 at 1:35 am

            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

    February 3, 2018 at 3:11 am

    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

      February 3, 2018 at 3:45 am

      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

        February 3, 2018 at 7:38 pm

        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
        • Catalin Bombea

          February 3, 2018 at 10:01 pm

          Great, glad to hear you managed to make it work.
          Regards,
          Catalin

          Reply
  5. Catherine (Cathy) Diethorn

    January 12, 2018 at 9:08 am

    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
    • Philip Treacy

      January 15, 2018 at 6:56 am

      Thanks Cathy 🙂

      Glad that you found it useful.

      Regards

      Phil

      Reply
  6. Simon Page

    October 14, 2016 at 4:25 am

    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

      October 17, 2016 at 11:17 am

      Hi Simon,

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

      Phil

      Reply
  7. Ronny

    May 30, 2016 at 8:17 pm

    Really helpful. Many thanks!

    Reply
    • Philip Treacy

      May 30, 2016 at 9:22 pm

      You’re welcome Ronny. Glad it was useful.

      Regards

      Phil

      Reply
  8. Mike

    December 25, 2015 at 5:33 am

    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

      December 26, 2015 at 1:57 am

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

    July 14, 2015 at 6:04 am

    This is great! Once selected, how do I capture the date and place it in a cell?

    Great work! Thanks,

    Randy

    Reply
    • Philip Treacy

      July 14, 2015 at 8:44 am

      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

        July 15, 2015 at 10:25 pm

        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

          July 16, 2015 at 12:25 pm

          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

            April 24, 2018 at 3:01 am

            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.

          • Catalin Bombea

            April 25, 2018 at 11:54 pm

            Thanks for feedback, glad to hear you found it useful.
            Cheers,
            Catalin

  10. Marteen

    July 13, 2015 at 4:43 am

    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

      July 13, 2015 at 3:12 pm

      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

    July 10, 2015 at 11:42 am

    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

      July 10, 2015 at 12:13 pm

      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 Cancel reply

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

Current ye@r *

Leave this field empty

Sidebar

More results...

Shopping Cart

mynda treacy microsoft mvpHi, I'm Mynda Treacy and I run MOTH with my husband, Phil. Through our blog, webinars, YouTube channel and courses we hope we can help you learn Excel, Power Pivot and DAX, Power Query, Power BI, and Excel Dashboards.

Subscribe to Our Newsletter

Receive weekly tutorials on Excel, Power Query, Power Pivot, Power BI and More.

We respect your email privacy

Guides and Resources

  • Excel Keyboard Shortcuts
  • Excel Functions
  • Excel Formulas
  • Excel Custom Number Formatting
  • ALT Codes
  • Pivot Tables
  • VLOOKUP
  • VBA
  • Excel Userforms
  • Free Downloads

239 Excel Keyboard Shortcuts

Download Free PDF

Free Webinars

Excel Dashboards Webinar

Watch our free webinars and learn to create Interactive Dashboard Reports in Excel or Power BI

Click Here to Watch Now
  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
 
  • About My Online Training Hub
  • Contact
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Copyright © 2023 · My Online Training Hub · All Rights Reserved

Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.

Download A Free Copy of 100 Excel Tips & Tricks

excel tips and tricks ebook

We respect your privacy. We won’t spam you.

x