Using Excel's Calendar Controls by David Hawley and Raina Hawley, authors of Excel Hacks 04/27/2004

One of the big problems many users encounter with Excel is incorrectly entering dates. If you want to ensure that users enter dates correctly, Excel's Calendar Control feature can make things easier for both you and the users of your spreadsheet.

Unless a date is entered correctly, Excel cannot recognize it as a valid date, which is a serial value or a number. This sometimes means you cannot perform calculations with the so-called dates. It also means any charts or pivot tables based on the dates will not be valid.

Let's first look at how we can use the standard Calendar Control to make dates easy to enter for a user and to ensure that the dates are entered correctly. Then we'll take a look at how an Advanced Calendar Control can be used to add or subtract days, weeks, or months from within our calendar.

Calendar Control

Open the workbook that you want to place the calendar in. It is a good idea to use your Personal.xls for this, in which case you should first go to Window > Unhide- PERSONAL.XLS. If this is grayed out it means you do not, as yet, have a Personal.xls. You can easily create one by recording a dummy macro. Go to Tools > Macro > Record new macro and choose Personal Macro Workbook from the Store macro in box. Then click OK, select any cell and stop recording. Excel will now have automatically created your Personal.xls. So now go to Window > Unhide- PERSONAL.XLS, and it should be there.

Now go to Tools > Macro > Visual Basic Editor (Alt+F11). Then go to Insert > UserForm from within the VBE. This should automatically display the Control Toolbox; if it does not go to View > Toolbox. Now, right click on the Toolbox and select Additional Controls. Scroll through the list until you see: Calendar Control 10.0 (the number will differ depending on the version of Excel you are using), check the checkbox and click OK. Now click the Calendar that is now part of the Toolbox and then click on the UserForm we inserted.

Related Reading Excel Hacks 100 Industrial Strength Tips and Tools By David Hawley, Raina Hawley

Using the size handles on both the UserForm and the Calendar Control, make them both a reasonable size. Now make sure the UserForm is selected, and then go to View > Properties Window (F4). Select Caption from the Properties Window and replace: UserForm1 with the word Calendar. Now go to View > Code (F7) and in the white Private Module in front of you, add the following code:

Private Sub Calendar1_Click()
    ActiveCell = Calendar1.Value
End Sub

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

Now go to Insert > Module and in this Public Module place this code:

Sub ShowIt()
    UserForm1.Show
End Sub

Next, click the top right X (or push Alt+F11) to return back to Excel.

Finally, go to Tools > Macro > Macros (Alt+F8) and then select ShowIt, click Options, assign a shortcut key, and you're done.

All you need to do now is push 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. This ensures that any date entered is valid and is certainly a lot easier for the user.

To see the finished product, go to the Download Demo workbook.

Advanced Calendar Control

To take things a step further, let's look at how we can turn our standard date entry calendar into an advanced one. When we have finished, we will be able to tell our calendar to add/subtract days, weeks and months.

OK, using the standard calendar control we have created, we need to increase the width of our UserForm to about 340 and the width of the Calendar itself to about 215. The height can be any reasonable height. Next you need to add (to the right in a vertical order) three label Controls (Label1, Label2, and Label3), as well as three TextBox Controls (TextBox1, TextBox2, TextBox3). Starting from Label1 give this a Caption of "Months to add," Label2 Caption="Weeks to add," and Label3 Caption="Days to add."

Then ensure Textbox1 is immediately below Lable1, Textbox2 is immediately below Lable2, and Textbox3 is immediately below Lable3. Now set the Value Property of each TextBox to 0 (zero).

Next to each TextBox (on the right) place a SpinButton, i.e SpinButton1, SpinButton2, and SpinButton3. These will be used to increment by Days, Weeks, or Months. Set the MIN Property of each SpinButton to -500 (or any negative amount) and the MAX Property to 500 (or any positive amount).

Next add a CommandButton Control to the top of the UserForm, to the right of Calendar1, and above all TextBox and Label Controls. Give this CommandButton a Caption of "Reset to Today's Date."

Now it's time for the code, so double click any Control to get to the UserForms Private Module and in here paste ALL the code shown below.

Dim dDate As Date

Private Sub CommandButton1_Click()
    Calendar1 = Date
    SpinButton1 = 0
    SpinButton2 = 0
    SpinButton3 = 0
End Sub
 
Private Sub SpinButton1_Change()
    If SpinButton1 >= -1 And SpinButton1 _
    <= 1 Then dDate = Calendar1

    TextBox1 = SpinButton1
    Calendar1 = DateAdd _
    ("m", TextBox1.Value, dDate)
    UpdateCell
End Sub


Private Sub SpinButton2_Change()
    If SpinButton2 >= -1 And SpinButton2 _
    <= 1 Then dDate = Calendar1

    TextBox2 = SpinButton2
    Calendar1 = DateAdd _
    ("ww", TextBox2.Value, dDate)
    UpdateCell
End Sub
 
Private Sub SpinButton3_Change()
    If SpinButton3 >= -1 And SpinButton3 _
    <= 1 Then dDate = Calendar1

    TextBox3 = SpinButton3
    Calendar1 = DateAdd _
    ("d", TextBox3.Value, dDate)
    UpdateCell
End Sub
 
Private Sub UpdateCell()
    ActiveCell = Calendar1
    ActiveCell.NumberFormat _
    = "dddd d mmmm yyyy"
End Sub

Once the code is in place, save, and then go back to Excel and show the calendar.

Again, the finished product can be found by checking out the Download Demo Workbook. You may also find Excel Date and Times and Convert Excel Date Formats useful when trying to understand and use dates and times in Excel.

David Hawley is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel.

Raina Hawley is a professional Microsoft Excel consultant whose company, OzGrid Business Applications, offers services in all aspects of Excel, and VBA for Excel.

O'Reilly Media recently released (March 2004) Excel Hacks.

• Sample Hacks are available free online.

• You can also look at the Table of Contents, the Index, and the Full Description of the book.

• For more information, or to order the book, click here.

Return to WindowsDevCenter.com.

Copyright © 2009 O'Reilly Media, Inc.