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
Enter your email address below to download the sample workbook.
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.
Szabolcs Kanyo says
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
Catalin Bombea says
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
Wietze Plantinga says
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
Catalin Bombea says
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
Wietze Plantinga says
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
Catalin Bombea says
Great, glad to hear you managed to make it work.
Regards,
Catalin
Catherine (Cathy) Diethorn says
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!
Philip Treacy says
Thanks Cathy 🙂
Glad that you found it useful.
Regards
Phil
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
EyeNoNothin says
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 says
Thanks for feedback, glad to hear you found it useful.
Cheers,
Catalin
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