I've written about using calendars/date pickers in userforms before but this one is particularly easy and flexible to use, even direct from a worksheet.
Thanks to our resident Excel guru Catalin for putting me on to it. It was developed by Trevor Eyre and you can read more about it here. You can download my workbook (at the bottom of the page) with working sample code for you to use and adapt.
I've modified the original code so that the calendar can now display the months and days in English, French, Spanish, German, Portuguese and Italian.
Thanks to Jorge Cabral for the Portuguese translation.
If you need another language, let me know in the comments below and I'll see if I can add it for you.
This date picker is implemented as a userform. All the code that makes it work is part of the form itself. No separate modules to worry about.
Using it is very simple. Add/import the CalendarForm form to your workbook, or make a copy of the workbook with the CalendarForm in it. Then call the form's GetDate function. This function returns the date you select.
There are a number of customizations you can make within the function call. For example you could change the first day of the week. The default is Sunday, but in my code I'm making it Monday
By default, months and days are in English, but you can also use French (fr), Spanish (es) or German (de) by specifying the language using the 2 character language code e.g. for Spanish
You can change the color that Saturday and Sunday dates are shown in by specifying RGB values
Check out the GetDate function in the CalendarForm module to see the other customizations you can make.
Using the Date Picker in a Userform
You might want to call the date picker from another userform. I've put together something very basic which is loaded by clicking a shape in the worksheet.
Clicking on the 'Choose Date' command button calls the CalendarForm.GetDate function which displays the date picker
You can call the GetDate function from any form element that you click on, it doesn't have to be a button.
Clicking on the month lets you choose the month. Clicking on the year lets you choose the year. Or you can use the scroll buttons to move backwards and forwards through the months.
When you click the date, that date is what the GetDate function returns and you can do with it what you wish. I've written code to put the date into a label on the form, a text box, and into cell A1 on the current sheet.
By placing the chosen date into a label, the date is not editable so you are forcing your users to use the date picker.
Anything in the text box is editable, so can be changed by your users, and this may mess up what you are trying to do.
The number of years displayed in the calendar is configurable. For example by specifying RangeOfYears:=2 when you call GetDate, the calendar will show 2 years either side of the current year. So in 2020, you'll be able to pick years from 2018 through to 2022.
NOTE: VBA handles dates in US format so I need to use the Format function to display the date the way I want, dd/mm/yyyy. You may have to do something similar if you don't use mm/dd/yyyy
Using the Date Picker From a Worksheet
You can call the date picker from the worksheet without needing to create another userform.
Clicking On An Object
Insert something on your sheet that you can assign a macro to, like a shape or an icon. Write a macro to call GetDate and assign that macro to your shape, icon or whatever it is you are using.
The following code is run by clicking a shape on a worksheet. The selected date is entered into cell A12.
Clicking on a Cell
By using a worksheet event you can display the calendar just by clicking in a cell (or a range of cells).
The VBA uses Intersect to check if the cell clicked is within the TriggerCells range, which is whatever range you wish to define. In my code I'm just using C29.
Thanks to Dan McGovern for suggesting and supplying this code.
Examples of all of these are included in the workbook you can download.
Excel Forms Create a simple user form [VIDEO]
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.
Hide, Delete and Sort Worksheets. Conveniently manage a large number of worksheets with a userform.
Add Data to Combo Box Drop Down List in Excel Form. Dynamically add elements to your combo box lists.