Excel Calendar Control to Any Excel Workbook

If you want to ensure that users enter dates correctly, the Excel Calendar Control can make things easier for both you and the users of the spreadsheet. With this tutorial, you can add the Calendar Control to any Excel workbook.

Unless a date is entered correctly, Excel won't recognize it as valid. This sometimes means you cannot perform calculations with figures that look like dates but aren't. It also means any charts or PivotTables based on these dates will not be valid. Although the use of Excel's very versatile validation feature can help with this, it is far from bulletproof.

With this tutorial, you can add the Calendar Control to any Excel workbook:

1. To start, open the workbook for the calendar.
2. It is a good idea to use your Personal.xls file for this, in which case you should first select View → Window → Unhide (pre-2007, Window → Unhide). If this option is grayed out, it means you do not have a Personal.xls file yet. You can create one easily by recording a dummy macro:
   1. Select Developer → Code → Record Macro (pre-2007, Tools → Macro → Record New Macro).
   2. Choose Personal Macro Workbook from the Store Macro In: box.
   3. Then click OK, select any cell, and stop recording. Excel will create your Personal.xls file automatically.
3. Next, press Alt/Option-F11 and then select Insert → UserForm from within the VBE. This should display the Control toolbox (if it doesn't, select View → Toolbox).
4. Right-click the Control toolbox and select Additional Controls. Scroll through the list until you see the Calendar Control 12.0 checkbox (the number will differ depending on the version of Excel you are using). Check the checkbox and click OK.
5. Click the calendar that is nowpart of the toolbox and then click the UserForm you inserted earlier.
6. Using the size handles on both the UserForm and the Calendar Control, size the UserForm and Calendar Control to a reasonable size.
7. Make sure the UserForm is selected and then select View → Properties Window (F4).
8. Select Caption from the Properties window and replace UserForm1 with the word Calendar.
9. Nowselect View → Code (F7), and in the private module, add the following code:

   Private Sub Calendar1_Click( )
    ActiveCell = Calendar1.Value
   End Sub
   
   Private Sub UserForm_Activate( )
    Me.Calendar1.Value = Date
   End Sub
   

10. Select Insert → Module, and in the public module, place this code:

    Sub ShowIt( )
     UserForm1.Show
    End Sub
    

11. Close the window to return to Excel, then save your workbook.
12. Press Alt/Option-F8 and then select ShowIt.
13. Click Options, assign a shortcut key, and you're done.

Just press your shortcut key, and the calendar will show with today's date as the default. Click any date and it will be inserted into the active cell.