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.
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.
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
Sample code is available here as an Excel .xlsm
Sharing is Caring
If you liked this or know someone who would like to learn how to create Excel forms, please click the buttons below to share it with your friends and colleagues.
Simon Page says
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!
Philip Treacy says
Hi Simon,
Not sure why an update would break this. But then, MS updates have a history of doing such things 🙁
Phil
Ronny says
Really helpful. Many thanks!
Philip Treacy says
You’re welcome Ronny. Glad it was useful.
Regards
Phil
Mike says
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
Catalin Bombea says
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
Randy says
This is great! Once selected, how do I capture the date and place it in a cell?
Great work! Thanks,
Randy
Philip Treacy says
Thanks Randy.
Once you select a date in the form, you can access it using
So if you wanted to put the date into A1, in your VBA you could write
Range("A1").Value = Calendar1.ValueCheers
Phil
Randy says
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
Philip Treacy says
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 Subwhich 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
Marteen says
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
Philip Treacy says
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
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 IfAt the top of the form code module you will also need to add this line
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
Col Delane says
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
Philip Treacy says
Hi Col,
If you open a Helpdesk ticket and attach your workbook generating the error I’ll look at it for you.
Regards
Phil