Excel Pop-Up Calendar

A Pop-up Calendar for Excel
Suitable for: Excel 2007, 2010
Click here for Excel 97, 2000, 2003

One of the biggest problems in maintaining "good" data is the entry of dates. People seem to get confused about entering dates. Should they enter dd/mm/yy or mm/dd/yy? Do they enter slashes or dashes or dots? And what was the date of the third Thursday in September last year anyway? What you reallyneed is a calendar!
NOTE: When I first wrote this tutorial I made use of the Microsoft Calendar Control, an ActiveX control that was installed along with Microsoft Office Professional (i.e. the version that included Microsoft Access). If you didn't have that version of Microsoft Office you could still download and install a copy of the ActiveX control. If you want to do that you can follow theoriginal tutorial here. Since then Microsoft have created an alternative to the Calendar Control called the MonthView control. This new ActiveX control is similar in function to the Calendar Control and has the advantage that it is installed with all versions of Microsoft Office. In Microsoft Office 2007 you have the choice of which ActiveX control to use but in Microsoft Office 2010 you must usethe MonthView control as described in this tutorial.
The tutorial shows you how to create a pop-up calendar using the Microsoft MonthView control that is installed with Excel. You will use the Visual Basic Editor to create a UserForm that displays a calendar. You will also write some VBA code to power the UserForm and to generate an additional item on the menu that appears when the userright-clicks a cell on an Excel worksheet. Clicking the menu item will display the calendar. When the user selects a date it is automatically entered into the active cell on the worksheet.

What Will the Calendar Do?
The MonthView control has a number of useful features. In its standard format it displays a single month in calendar format (it can be set to display more than one if required). Either sideof the month name is an arrow button which displays the previous or next month when clicked. Clicking on the month name opens a list of months so that you can quickly jump to a specific month. Clicking on the year number reveals a spinner which lets you change the year. The current date is always shown at the bottom of the calendar. Click it to jump to today's date on the calendar.
| | | |Where Does the Code Go?
The pop-up calendar is created entirely with code and is generated when you need it. The code that creates and operates the calendar has to reside inside an Excel file. The question is which one? If you want the pop-up calendar to be available whenever you are working in Excel you should create it in Personal.xlsb, sometimes referred to as your Personal Macro Workbook,because this file exists to store code that you want to be available to all your Excel files. Personal.xlsb opens and is hidden each time Excel is started. Any macros and functions it contains are then available for use in any other workbook. To find out whether or not you already have a copy of Personal.xlsb read the instructions in New to VBA? further down the page.
However since the PersonalMacro Workbook, as its name implies, is specific to your copy of Excel it will reside either on your computer's hard drive or within your personal profile on the network. It isn't the best place to put the code if you want other users to have access to the pop-up calendar. Instead, you could create your pop-up calendar in a specific workbook. It would be available whenever (and only when) thatworkbook was open, but also available to any other workbook that was open at the same time. A pop-up calendar created in an Excel template would be present in each workbook that was generated from the template. The most flexible option is to create an Excel Add-In. It's easy to do and will allow you to distribute your calendar to other users. You start by building the calendar exactly as described in...