The first part of this tutorial A Pop-up Calendar for Word Part 1: A Simple Calendar showed you how to build a very basic calendar tool. This tool works well but, because it is simple, it is also limited in function. The date format is "hard-coded" into the calendar and can't be changed by the user unless they have a knowledge of VBA.
This tutorial improves on the basic calendar by allowing the user to choose a date format from a number of pre-defined options, and to specify their own custom format if they wish. It allows a chosen format to be set as the default by storing the calendar settings in the Windows Registry.
Here are the main features of the improved pop-up calendar tool:
- As with the Simple Calendar, this tool can be called up using a keyboard shortcut or from an option on the right-click context menu.
- The current date is displayed by default. The user can browse for a date using the calendar.
- The user can choose the format in which the date will be entered into their document. Any date format can be set as the default.
- A custom date format can be specified and saved as the default to save time next time.
- The form displays a sample of the date in the chosen date format.
- Once set up the tool can be used to enter dates into a document quickly and easily. To enter today's date in the chosen saved format you just have to press CTRL+SHIFT+C, ENTER (or whatever keyboard shortcut that you have specified followed by ENTER).
About Date Formats
VBA uses the standard method of defining date formats that you are probably familiar with from other programs such as Microsoft Excel. If you are not familiar with the codes used, follow this link to see an explanation and some examples (the link displays the help text that will be included later in this project).
Building the Calendar Form
The first part of this tutorial explained the basics of building a VBA UserForm and showed how to locate and insert the ActiveX Calendar Control. This tutorial assumes that you have mastered the (simple!) skills of form building and will concentrate on the VBA code necessary to make everything work.
To make sure that you have all the objects necessary, and that they all have the same names as I use when referring to them in the code, build your form to look like the one illustrated below (you are free to design your calendar any way you like, but make sure that you modify your code to suit):
A Note About Using Frames on UserForms
You can see that I have used frames to divide the UserForm into sections and arrange the controls into visual groups. This is a cosmetic feature and does not affect the performance of the form. When using frames like this it is usually easier to place a frame on the form before placing the objects that will appear inside the frame. Then, if you move the frame the objects inside it will move too and retain their correct positions in relation to it.
Set the Properties of the Controls
As you add the various objects (controls) to the form apply the settings shown in the table below to their properties. To do this, first select the control then find the appropriate property in the Properties Window (normally in the lower left corner of the Visual Basic Editor). If the Properties Window is not visible, switch it on from the View menu. Set the Name property of each control according to the illustration above, then set the following:
|Frame1||Caption||Select a date:|
|Frame3||Caption||Select a date format:|
|Frame4||Caption||Custom date format:|
|lblSample||Caption||Monday 01 January 2001|
|Set as default |
Now the form is complete, and it's time to start writing the VBA code to power it.
What the Code Has to Do
In the first part of this tutorial, the code for the basic calendar didn't have to do very much. When the user chose a date the code had to write the date, in a predefined format, into the current document then close the calendar form.
In this tutorial the code has a lot more to do because it is offering much more functionality:
- It must convert the user's selections into an acceptable date format.
- It must display a sample of the date in the specified format.
- It must be able to store and retrieve the user's preferences.
- It must write the date, in the user's specified format, into the current document and close the calendar form.
In addition, I have added help for the user in the form of an additional UserForm displaying information about date formats.
Download the Code
I believe that if you type out the code yourself it will help you learn the VBA, but there's quite a lot of code here so you might prefer to copy the whole lot and paste it into the form's code module. Follow this link to open a window containing the entire code module for the calendar form.
To get the code, click the link above to open the web page. Click anywhere on the page and press CTRL+A to select all the text, then CTRL+C to copy it. Switch to your Visual Basic Editor and click on the calendar form to select it. Press F7 (or go toView > Code) to open the form's code module then press CTRL+V to paste the code into the module. To make sure everything got copied properly, go to Debug > Compile Project. The compiler will highlight any problems.
I hope you are here because you want to learn about VBA! So read through the next section to find out what each piece of code does and why it needs to do it...
Writing the VBA Code
This section explains what all the various bits of code do, why they are necessary, and will help explain the logic behind my VBA code writing.
A Function to Create a VBA Date Format
Near the top of my code is a function that I have called DateFormat. I wrote the function because its code is used many times by different procedures. Placing the code into a function like this makes re-using it much easier and saves a great deal of typing! The purpose of this function is to turn the choices that the user has made from the various check boxes and option buttons into a usable VBA date format. Each section of the date (dayname, daynumber, month and year) are analysed with an If Statement and an appropriate code defined. Here is the one used for the months:
Note that an appropriate separator is defined depending on the user's choice. I have only done this for the month If Statement because (in UK English) it is most common to use a slash (/) or a space to separate the parts of a date. If the month is written as a number the date parts are normally separated by slashes (e.g. 27/9/2003 or 27/09/2003) but if it is written as text they are normally separated by spaces (e.g. 27 Sep 2003 or 27 September 2003). The separators are not normally used the other way (e.g. 27 09 2003 or 27/Sep/2003).
NOTE: If you live, work or are building your calendar for use in a country that uses a different date-writing convention you can change the separator definitions to suit your requirements. But you can't always anticipate the user's needs, so if you can't decide what to do, remember that this calendar tool also allows the user to define and save their own custom date format.
After all the choices have been analysed they are assembled into a single date format code:
NOTE: I have used the European convention of writing dates in the order day-month-year. If you use a different method (e.g. US month-day-year) you can rearrange the code accordingly (e.g. for US swap the positions of strMonth and strDayNumber).
Here is the complete code for the function [click the thumbnail to see a full-sized image]:
Check Boxes and Option Buttons
According to the conventions form design, check boxes and option buttons do different things. If you are used to building forms in Access you will find that they work in a different way there than in a VBA UserForm.
|I placed my option buttons and check boxes inside a frame. This has no effect on how they work (unlike Access forms). On a VBA UserForm option buttons can work as a group but they don't do so automatically (unlike Access forms). When they work as a group, the act of selecting one member of the group deselects the others. If you want option buttons to work as a group, you must define the group by assigning the same value to the GroupName property of each member of the group.|
Check boxes are different. They can't work as a group (unlike Access forms) even though they have a GroupName property (programmers make use of this property for other purposes).
I have arranged my option buttons in logical groups. The user has only two choices of how a year is displayed so there are two option buttons in the Year group. There are four ways a month can be displayed so there are four option buttons in the Monthgroup. But although there are four ways to display a day (short or long number, short or long name) the user might want to choose one or two of the options, so a single group won't do.
I have included the first two option buttons (short and long number) in the group DayNumber. I wanted the other two choices to look different so I put them on the form as check boxes, but in order to make them work as a group - so that when one was selected the other was deselected - I needed to write some code. Something else had to be taken into consideration. You can deselect a selected check box by clicking it (click once to add the tick, click again to remove it). My code had to work in such a way as not to interfere with this.
Coding the Check Boxes
I wanted the two DayName check boxes to act as a group, as if they were grouped option buttons. If one is already chosen and the user and the user clicks the other, then the choice moves to the one they just clicked (i.e. the tick is removed from the other). But I still need to retain the characteristic behaviour of check boxes, that a selected check box can be deselected with a click. The following code is attached to the AfterUpdate event of each check box (with the check box names changed accordingly):
The If Statement looks at the updated state of the check box and, if it is True (i.e. contains a tick), it sets the value of the other check box to False (i.e. no tick). This makes sure that the situation can not arise in which both check boxes contain a tick, but does allow a situation where both do not contain a tick.
There is also a line which writes a caption into the lblSample label (below the calendar) so that it displays an updated example of the date format to include the change that the user has just made. Note that this uses the DateFormat function described above.
Coding the Option Buttons
The option buttons don't really need to do anything. Another procedure (the DateFormat function) reads their values so that a format code can be assembled. But, as with the check boxes, each time the user makes a selection that change needs to be reflected in the example date displayed by the lblSample caption. Each option button has exactly the same line of code attached to its AfterUpdate event. Here's an example:
The Custom Date Format Tool
This tool allows the user to devise a date format of their own design using the standard date formatting codes. The tool consists of a check box and a text box working together. To enter a custom code the user types directly into the text box. As they type their changes generate an example date displayed by the lblSample caption:
[Point at the image above to play the animation]
This is achieved by code running on the Change event of the txtCustom text box. Whenever the contents of the text box change this code examines the value of the text box:
If the text box contains some text the code sets the value of the chkCustom check box to True and generates an example date for display in lblSample using the contents of the text box for the format code. As the animation above shows, this sample is generated and modified as the user types each letter of their format code so they can see exactly how their typing builds the date.
If the text box becomes empty (i.e. its value is "") the code sets the value of the chkCustom check box to False and has the lblSample caption display an example date using the current settings in the "Select a date" section.
Because these changes cause the value of the chkCustom check box to change, this causes additional changes to take place on the form. I decided that simply allowing the user to type a custom format into the text box wasn't enough. I wanted it to be clear to the user that they were either applying a custom format or not. For this reason, when the chkCustom check box is ticked the txtCustom text box is enabled but all the formatting option buttons and check boxes in the "Select a date format" section are disabled. If the chkCustom check box is not ticked those option buttons and check boxes are enabled, and the txtCustom text box is disabled.
These changes are made by the following procedure running on the Change event of the chkCustom check box:
To simplify the procedure, and reduce the amount of typing involved I created a custom function containing the long list of commands to either enable or disable all the date format check boxes and option buttons. The function, which I called EnableControls, accepts True or False depending on whether the controls are to be enabled or disabled. See the beginning of the full code listing for the function code.
Disabling the txtCustom text box does not remove its contents. This is because the user might choose to set a custom date format as their default date format, but not necessarily want to use it every time. Allowing it to remain in place saves them from having to type a commonly used formatting code every time they want to use it. In the illustration below, the custom code is greyed out because (as determined by the check box) custom formatting is not currently enabled.
Setting a Default Date Format
The whole point of a pop-up calendar is that it makes entering a date quick and easy. People are most likely to write their dates the same way each time. If the they have to specify the format every time, the process becomes less convenient. To solve that problem I have added a "Set as Default" tool.
There are various ways to save user preferences. I have two preferred methods: saving the information in a text file, and saving the information in the Windows Registry. Both are easy to achieve using VBA. For this project I have chosen to use the Registry. Here's how it works...
The principle is very simple. When the user has defined a date format they click the "Set as Default" command button. The procedure records the value of each check box, and option button as well as the custom date format check box and text box. Each value is written to the Registry. Here's the code that does it:
The procedure loops through all the controls on the form. The If Statement checks the control's type and if it is a CheckBox, OptionButton or TextBox it records the control's name and value in the Registry. When you use the Registry from VBA like this you don't need to get involved with editing the Registry yourself, the code does it all for you (but please read my note on Working with the Registry below). In case you're interested, the Registry entry will look something like this:
In the procedure shown above this is what the various bits of code mean:
SaveSetting - This VBA method means "record the following information in the Registry" and it has the following four arguments...
AppName - Essentially a category (in fact a folder) in which the information will be stored. I have called mine "Fontstuff" - you can call it anything you like. If the AppName folder does not already exist the procedure creates it.
Section - A sub-folder within the AppName category. I have called it "WordPopupCalendar". It makes sense to give it a meaningful name. If the Section folder does not already exist the procedure creates it.
Key - This is an object within the section folder which can store a value. The code procedure creates a key (if one does not already exist) for each of the controls on the form (as defined by the If Statement) and gives the key the same name as the control.
Setting - This is the value stored by the key. The code procedure assigns the value of the control to the key with the same name.
The illustrations below show a complete set of date format settings on the form and the resulting Registry entries [click the thumbnail to see a full-sized image]:
Folders created by the VBA SaveSetting method are always located in the same place in the Windows Registry:
My Computer\HKEY_CURRENT_USER\Software\VB and VBA Program Settings\
Applying the Default Settings with the Initialize Event
When a VBA UserForm opens its Initialize event happens. I have used the UserForm_Initialize event procedure to set up the form and to retrieve any default settings from the Registry. Here's the entire procedure [click the thumbnail to see a full-sized image]:
First, the code checks the user's selection (in case they selected some text before they called up the calendar) and if can be read as a date, it sets the calendar to match that date. If it doesn't recognise the selection as a date, or if no text is selected, it sets the calendar to the current date. It then enables the form's OK button:
Next it uses the GetSetting method to check the Registry for any default settings:
The VBA GetSetting method is used to retrieve values from the Registry. In the same way as before, when the value of each control was stored in the Registry using the SaveSetting method, the values have to be read one-at-a-time.
First of all the If Statement tries to read the value of one of the controls (I have chosen to use chkddd but any one would do). If no value can be found for that control a value of "" is returned. This means that there can be no default settings in the Registry, so the first part of the If Statement makes a number of settings itself (I have chosen m/d/yy). If, on the other hand, a value is returned by the initial GetSetting enquiry, the second part of the If Statement loops through the form's controls using GetSetting to retrieve the value of each one from the registry, and applies that value to the control.
Finally, because of the order in which the code loops through the controls (option buttons, then check boxes and finally the text box), if there is a value for the txtCustom text box the process of entering the value sets the chkCustom check box to True - even though its stored value may be False! This is annoying... but these things happen in VBA. So, there is an additional line that retrieves the value of the chkCustom checkbox again to make sure it is correct.
The last line of the procedure writes a caption for lblSample to match the calendar's date and the format settings:
Coding the Calendar Control's Events
In the first part of this tutorial, the calendar code had only one function to perform. When the user chose a date, that date was entered into the document and the calendar closed automatically. Their choice triggered the control's Click event and the date-writing code was located in the Calendar1_Click procedure.
The requirements are slightly different here. We do not need the calendar to do anything other than "remember" the user's choice. The date will not be written to the document until they click the form's OK button. But we do require any change of date to be reflected in the lblSample example. Here's the revised Calendar1_Click procedure:
Notice that the last line of the Calendar1_Click procedure enables the form's OK button. This is because there are two event procedures which will disable it: the NewMonth and the NewYear event.
You might expect that, when you change the calendar's month or year by choosing one from the drop-down lists at the top of the calendar control, the dame day number would remain selected. But is doesn't - the result is that no day number is selected (don't ask me - they just programmed it that way!). In the simple calendar that didn't matter because the act of choosing a day fired the calendar's Click event which wrote the date into the document. Here that isn't the case, so we have to make sure that the user doesn't click the form's OK button until they have chosen a complete date (i.e. they have chosen a day number).
Fortunately there are NewMonth and NewYear events that fire when the user chooses a new month or a new year and these can be used to disable the OK button. As mentioned above, the OK button is enabled again when the user chooses a day number, causing the Calendar1_Click event to fire.
Coding the Command Buttons
Having chosen a date and a suitable format, the user can insert the date into their document by clicking the OK button (cmdOK); they can close the calendar without entering a date by clicking the Cancel button (cmdCancel); or they can ask for more information by clicking the Help button (cmdHelp).
The Default property of the cmdOK button has been set to True so that if the user presses the Enter key on their keyboard it has the effect of clicking the OK button. Similarly, the Cancel property of the cmdCancel button has been set to True so that if the user presses the Escape key on their keyboard it has the effect of clicking the Cancel button.
The Cancel Button
The Click event of the Cancel button is used to close the form without making any changes to the document:
The OK Button
The Click event of the OK button writes the chosen date to the document, but first it uses an If Statement to look at the state of the chkCustom check box. If the check box is ticked it formats the date with the custom format; if not it formats the date using the settings in the "Select a date format" area. Having written the date into the document it moves the user's cursor to the end of the date entry and closes the form:
The Help Button
You can choose to omit the Help button if you want, but I think it is useful so have included one. Usually, clicking a button marked Help invokes the Windows Help system. Compiling custom help is not a simple matter and a small project like this doesn't really justify the effort. so instead I have supplied help in the form of another UserForm (see below). The UserForm is called frmHelp and the Click event of the Help button simply displays this form:
Building the Help Form
The Help Form (frmHelp) is very simple, containing a frame (Frame1) inside which is a label (Label1) displaying the help text. There is a command button (cmdExit) to close the form.
Build the form to look like the illustration above and set the Name property of each control according to the illustration above, then set the following:
|Frame1||ScrollBars||2 - fmScrollBarsVertical|
|ScrollHeight||550 (see note below)|
Draw the frame first then draw the label inside it. When entering a lot of text into a label like this, you will probably find it easier to type directly on to the label rather than into the Caption property box. To make a new line in your text pressSHIFT+ENTER.
The frame's scrollbar does not automatically scroll far enough to display all the text so you have to adjust the ScrollHeight property of the frame to make it fit.
If you do not want to make up your own help text you can follow this link to copy the text used in this example.
The Help Form needs only two small pieces of code. The UserForm_Initialize procedure sets the ScrollTop property of the frame to zero, ensuring that it opens displaying the beginning of the help text:
The cmdExit_Click procedure closes the form when the Exit button is clicked:
Because the Cancel property of the cmdExit button was set to true the form will also close if the user presses the Escape key on their keyboard.
Coding the Word Document
Opening the Calendar
All the code which follows can be placed into the document's ThisDocument code module. For the user to be able to make use of the calendar tool, it is essential to include a macro to display the calendar on demand:
You can leave it at that if you want, and the user can open the calendar by running the OpenCalendar macro from the menu in the normal way (Tools > Macro > Macros...) but since the whole point of this project is to make work easier for the user it makes sense to make opening the calendar a simple as possible...
Adding A Keyboard Shortcut and a Menu Item
The following procedure defines a keyboard shortcut for running the macro (and thus opening the calendar), and creates a new entry on context menu that appears when the user right-clicks on the page:
The first part of this procedure assigns the keyboard shortcut CTRL+SHIFT+C. You can choose your own keyboard shortcut by changing the keycode constants specified. For a list of suitable keycodes ask VBA Help about the BuildKeyCode method and follow the link to WdKey.
The second part of the procedure creates the new menu item "Insert Date". Again, you can change text of the menu item (the caption) but make sure that the OnAction property refers correctly to the name of the macro that opens the calendar.
Before the instruction to create the menu item I have included a line of code to delete the same menu item! The reason for this is that in some circumstances, for example if the document closes incorrectly because Word has crashed, the menu item might not be removed as it should be. This line makes sure that the menu item is not duplicated. The inclusion of the statement On Error Resume Next tells the program to ignore the error that would otherwise occur if the code tries to delete a menu item that isn't there.
NOTE: I have assumed that the document that is being created here will be saved as a Word Template and installed as an Add-In (instructions for doing this follow below). In this case the above code is contained in the Document_Open procedure. If you are going to include the code in a regular template (i.e. you will not install it as an Add-in) you should put the above code in the Document_New procedure.
Removing the Keyboard Shortcut and Menu Item
When the template closes, the keyboard shortcut should be cancelled and the menu item removed. This is achieved by the following short code procedure:
Save the Calendar Template
What you do with the word document now depends upon when you want the calendar to be available. If you want the calendar to be available to all your documents whenever you use Word, then the document should be saved as a Word Template (*.dot) file.
Go to File > Save As, give the file a suitable name (mine is called WordPopupCalendar) and change the Save as type: setting to Document Template (*.dot).
To install the template as a Word Add-in it must be located in Word's Startup folder. For instructions on how to do this, refer to the section "Installing the Calendar" in the first part of this tutorial. The procedure is exactly the same.
You may prefer not to install the template as a Word Add-In and instead use it as a regular template. If you use the template like this, the calendar will only be available to documents based on that template, and not all your Word documents.