|   Home    |    Excel    |    Access   |   Word   |   Outlook   |   FrontPage   |   VBA   |   Downloads   |   Index   |
Mailbag...

 

I Want the Calendar to Open Automatically When the User Enters a Form Field

Many people use Form Fields in their Word Templates. When combined with a standard document such as a form letter, form fields offer a convenient way for the user to move quickly to those parts of the document where information has to be inserted. When you build a template containing form fields, the last step before saving the template is to apply protection to the document. This "locks" the document so that the only parts of it that can be changed are the form fields. Protecting the document effectively turns the document into an on-line form. The user moves from field to field by pressing the Tab key or by clicking directly on the fields themselves.

Using the Forms toolbar to protect a Word document

My tutorial A Pop-up Calendar for Word shows you how to build a pop-up calendar that can be summoned by running a macro from a keyboard shortcut or a command on the right-click context menu. When the user chooses a date from the calendar the date is placed into the active document at the insertion point. The tutorial shows how to create the calendar in the Normal template, or in a Word Add-in, so that it is available to any document.

If you have a protected document containing form fields, one or more of these fields might require a date to be entered and you might like to make use of your new pop-up calendar. Unfortunately it doesn't work! If you try to call the calendar from a protected form an error results because the code, as it stands, cannot manipulate the protected document. But with a few changes to the document and the code the calendar can be made to appear automatically when the user enters a chosen field. Here's how to do it...

Give the Template Its Own Calendar

The code that powers the calendar needs to be changed so that it can work with a protected form. If you already use the calendar in your other documents you won't want to lose that functionality, so you will need to create a new calendar in your form template. That doesn't mean starting from scratch! You can export a copy of the calendar UserForm (along with its accompanying VBA code) from its existing location (e.g. Normal.dot) then import it into your form template.

In the Project Explorer window of the Visual Basic Editor right-click on the existing calendar UserForm and choose Export and save the file to a temporary location (it doesn't matter where). Then right-click on the name of your new template, choose Import, and locate the file you just exported:

Export a copy of the calendar UserForm from its current location Import the calendar UserForm to the new template

This moves a copy of the calendar from the original file to your new template. The next step is to modify the code to make it work in conjunction with the form fields.

NOTE: If your existing calendar UserForm is in a template loaded as a Word Add-in you will find that the Visual Basic Editor will not let you view its code. You should first unload it in Word by going to Tools > Templates and Add-ins, removing the tick next to the template's name, and clicking OK. Then open the file using Word's File > Open command. When you return to the Visual Basic Editor you will find that you can view the code. When you finish you can close the file and reload it from the Templates and Add-ins dialog.

Modify the Calendar Code

You need a macro to open the calendar. This can be placed in the ThisDocument module of your template. You should give the procedure a different name to the one that opens your other calendar (if you have one). In my tutorial A Pop-up Calendar for Word I suggested the name OpenCalendar so I'm using the name OpenFormFieldCalendar here to avoid confusion:

This procedure displays the calendar UserForm

The calendar code itself needs to be modified to enable it to work with the protected form. First, the Calendar1_Click procedure which returns the user's chosen date to the form field. This code gets the name of the current field (the one that the user just entered and which called up the calendar) and writes the chosen date back into that field:

This procedure enters a date into the active Form Field

The code which initializes the UserForm needs to be changed too. The modified code looks at the active form field to see if it already contains a date. If it does the calendar opens showing that date. If not, or if the current entry is not recognised as a date, the calendar displays the current date:

This procedure sets the calendar's date as it opens

If you prefer, you can omit the UserForm_Initialize procedure altogether. This will not affect the main function of the calendar.

The cmdClose_Click procedure remains unchanged. Although the Close command button is not visible (it is hidden behind the calendar) the button and its procedure enables the calendar to be closed without entering a date by pressing the Escape key.

This procedure closes the calendar

Modify the Form Field Properties

If the calendar is to be displayed automatically when the user tabs into, or clicks on a form field then the form field's properties must be modified. With the form template open in Word, first make sure that the form's protection is switched off by choosing Tools > Unprotect Document or click the padlock button on the Forms toolbar. Then right-click on the field and choose Properties (or click on the field and click the Properties button on the Forms toolbar). This displays the Properties dialog for the selected field. In the section marked Run macro on open the Entry: list and find the name of the macro you created to open the calendar UserForm (in this example it is called OpenFormFieldCalendar) and choose it.

Choosing a macro to run when the user enters the selected field

Do this for each of the form fields for which you want the calendar to appear. You can apply it to as many fields as you require because the calendar's code detects which form field has called it, and knows which one it should enter a date into.

There are other form field properties that you might find useful. Setting the Type property to Date ensures that the field will only accept a date (in case the user dismisses the calendar and types an entry themselves). Doing this also lets you set a default Date format property for the field. This will override the date format specified in the Calendar1_Click procedure. You can also Add Help Text that will be displayed either in the status bar (when they enter the field) or in a help dialog (when they press the F1 key - the calendar must be closed for this method to work).

Protect and Save the Document

Before these changes take effect you must protect the document by either clicking the Protect Document button on the Forms toolbar or by going to Tools > Protect Document > Forms. Close and save your template to complete the modifications.

^ top
 

 

 

 

 

Hit Counter