Using Excel's Calendar Controlsby David Hawley and Raina Hawley, authors of Excel Hacks
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.
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.
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.
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 (
Label3), as well as three TextBox Controls (
TextBox3). Starting from
Label1 give this a Caption of "Months to add,"
Label2 Caption="Weeks to add," and
Label3 Caption="Days to add."
Textbox1 is immediately below
Textbox2 is immediately below
Textbox3 is immediately below
Lable3. Now set the
Value Property of each TextBox to
Next to each TextBox (on the right) place a SpinButton, i.e
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
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.
For more information, or to order the book, click here.
Return to WindowsDevCenter.com.
Copyright © 2009 O'Reilly Media, Inc.