Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms Free

May 21, 2020 Code VBA, Free Excel Templates,

Multi-Language Excel Calendar (Date Picker) for Worksheets and Userforms Free

Excel Calendar (Date Picker) to Use in Worksheets and Userforms

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.

Multi-Language Support

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

calendar date picker customize first day of week

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

calendar date picker customize day colors

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.