Custom Excel Date Picker
Issue with the MonthView Control
Click to Download the Custom Excel Date Picker
Excel does not really provide a good way for the user to pick a date from a calendar when your application is running, for my applications I had to come up with Custom Excel Date Picker. Yes, you could use the Monthview control but over the years I have found it somewhat unreliable and on most machines it is simply not available by default. You would need to browse the internet and look for the mscomct2.ocx file and then use the command prompt to register it (or ask your IT department to do it for you if you do not have admin privileges, which makes it a bigger pain). The other issue I have found is that if someone who does not have this file installed opens the application to which you added the MonthView control, the MonthView control will simply be deleted by Excel and you need to add it again (what a pain).
My Date Picker version
My version offers more flexibility and relies solely on the userform controls which come installed by default on all Excel versions. Below I will go through the parameters you can customize.
You can choose a Start Date
By using the .StartDate property you can choose the day that the Custom Excel Date Picker will show when it first loads up. If you omit this parameter then it will default to the current day.
You can choose a Minimum Date and Maximum Date
By using the .MaxDate and .MinDate properties you can tell the Custom Excel Date Picker not to scroll, respectively, after the month of the MaxDate or before the month of the MinDate. For instance if you choose as MaxDate 13 November 2019, the Date Picker will not scroll beyond November 2019. I found this feature quite useful if you want to restrict the range of dates a user can pick.
You can choose the weekdays order
By using the property WeekBeginsOn, you can establish the order of the days of week with which the date Picker shows the calendar. If vbMonday then dates are shown from Monday to Sunday. If vbSunday then dates are shown Sunday to Saturday. If vbFriday then from Friday to Thursday. If you omit this property then it defaults to vbUseSystemDayOfWeek (based on local Windows settings).
You can unable the user to pick a date outside the current month
If you set the property AllowUserToSelectDaysNotInMonth to FALSE then the user will not be allowed to select a date which is outside the current displayed month. For instance, if the Date Picker is currently displaying February 2018, the user cannot select the dates End of January or Beginning of March.
You can unable the user to pick a day that falls in a weekend
By setting the DenyWeekendsSelection property to TRUE the user will not be able to select Saturdays or Sundays.
Let me know what you think or some features you would like to see added in the comments below.