Thursday, 7 July 2016

Calendar (Date Picker) Control Alternative

When you want to add a calendar to a userform, you can try to make use of the Microsoft Date and Time Picker Control. Unfortunately this control is not 100% reliable and a number of people have decided to make their own.

In my case, I was asked by a client the other day to add a calendar. I decided to tweak a calendar I made way back in 2005. That calendar is based on a formula I saw on Daily Dose of Excel by John Walkenbach.

Anyway, I had a think about what I should try to imitate with regards to the date picker control. First off, it's compact. After all, you don't want a single control to overwhelm your main form.

So I made this.



You can change the week start date to either Sunday or Monday, as well as show/hide ISO week numbers. (I used to work in International Sales and I used the ISO Calendar to refer to week numbers for use in logistics with clients and overseas representatives. So I thought it would be a good idea to include this functionality. The ISO calculation uses a formula based on one by Evert van den Heuvel.)







The date you select becomes dtCalendar in the clsDateLabels module.


I also decided to add some shortcut keys:

Alt + Q: Show the next month
Alt + W: Show the previous month
Alt + E: Show the next year
Alt + R: Show the previous year
Alt + T: Show this month and year
Alt + O: Show/hide options
Alt + I: Show/hide ISO week numbers
Alt + S: Start the week from Sundays
Alt + M: Start the week from Mondays
Alt + C: Show the calendar (if not visible)
Alt + X: Close the calendar (if visible)

A reasonably useful calendar.

But then I thought that a lot of people don't need any options, so I made 2 more versions which are even more compact.

This one is a simple calendar.



You can change the week start date by going to the mCalendar module and changing vbSunday to vbMonday.



And this is one that shows ISO weeks.



Shortcut keys for both the simple and ISO calendars:

Alt + Q: Show the next month
Alt + W: Show the previous month
Alt + E: Show the next year
Alt + R: Show the previous year
Alt + T: Show this month and year
Alt + C: Show the calendar (if not visible)
Alt + X: Close the calendar (if visible)

Download them here.



No comments:

Post a Comment