Automatic Document Template for Word
An Introduction to Microsoft Word UserForms
Part 3: Writing the Code
Introduction
The next step is to add the VBA code that will power the form.
Five different code procedures (sometimes called Macros) need
to be written. First, a procedure to "initialize" the form when it
opens; a procedure to respond to the clicking of each of the three
command buttons; and a procedure to open the form automatically when
the document is opened.
Initializing the Form
In the Visual Basic Editor open the form's code window by
selecting the form and pressing the F7 key. Alternatively
right-click the form's name in the Project Explorer pane and choose
View Code from the shortcut menu. At the top of the code
window there are two drop-down lists, General and
Declarations. Open the General list, scroll down and
choose UserForm then open the Declarations list and
choose Initialize. You might notice that the Visual Basic
Editor tries to anticipate your requirements and also enters the
text: Private Sub UserForm_Click()
End Sub
Unfortunately this isn't what you need but you can safely ignore
it or, if you prefer you can delete it (the Visual Basic Editor will
delete it anyway later, if it remains unused, when you compile your
code).
The Visual Basic Editor has written the first and last lines of
the UserForm's Initialize event procedure:
Private Sub UserForm_Initialize()
End Sub
Any code you type for this procedure should be entered between
these two lines.
The Initialize event occurs when the form opens, so you can make
use of it to set up the form ready for use, by setting any default
values for textboxes or option groups, and filling the lists of any
combo boxes.
It is usually a good idea to set a default value for an option
group. It reminds the user that a choice has to be made. All you
have to do it set the value of one of the option buttons to true
with this line of code:
optGreeting1.Value = True
NOTE: It is good code-writing practice to press the
Tab key before typing a line of code to indent the line from
the margin (see the completed code procedure below). This makes code
much easier to read later.
When filling the list of a combo box, each item should be added
in the order that you want it to appear:
With cboInterviewLocation
.AddItem "London"
.AddItem "San Francisco"
.AddItem "Lunar Station"
.AddItem "Jupiter Station"
.AddItem "Deep Space 7"
.AddItem "Deep Space 9"
End With
NOTE: This is called a "With statement". It saves
you having to write "cboInterviewLocation" each time you add an
item. "With statements" save time in code writing and help the code
run more quickly.
To set an initial value for a textbox enter a line similar to
this:
txtInterviewTime.Value = "10.00 am"
Here is the finished UserForm_Initialize procedure for the
example form.
Private Sub UserForm_Initialize()
optGreeting1.Value = True
With cboInterviewLocation
.AddItem "London"
.AddItem "San Francisco"
.AddItem "Lunar Station"
.AddItem "Jupiter Station"
.AddItem "Deep Space 7"
.AddItem "Deep Space 9"
End With
With cboInterviewDay
.AddItem "Monday"
.AddItem "Tuesday"
.AddItem "Wednesday"
.AddItem "Thursday"
.AddItem "Friday"
End With
With cboInterviewDuration
.AddItem "½ hour"
.AddItem "1 hour"
.AddItem "2 hours"
.AddItem "all morning"
.AddItem "all afternoon"
.AddItem "all day"
End With
With cboSenderAddress
.AddItem "London"
.AddItem "Jupiter Station"
.AddItem "Deep Space 9"
End With
End Sub
Coding the 'Clear' Button
Clicking this button will restore the form to its original
values. All the textboxes and combo boxes will be emptied (i.e.
blank) and any option groups reset. Use the drop-down lists at the
top of the code window to create a cmdClear_Click procedure.
NOTE: Another way to open an event procedure for a
control is to double-click the control in the form design window.
Here's the procedure code for the example form:
Private Sub cmdClear_Click()
optGreeting1.Value = True
txtRecipientName.Value = Null
txtRecipientAddress.Value = Null
txtSalutation.Value = Null
txtPosition.Value = Null
cboInterviewLocation.Value = Null
cboInterviewDay.Value = Null
txtInterviewDate.Value = Null
txtInterviewTime.Value = Null
cboInterviewDuration.Value = Null
txtSenderName.Value = Null
txtSenderPosition.Value = Null
cboSenderAddress.Value = Null
End Sub
Coding the 'Cancel' Button
This button allows the user to quit without creating a letter.
Clicking the Cancel button will close the UserForm and throw away
the active document (the unfinished letter):
Private Sub cmdCancel_Click()
Unload Me
ActiveDocument.Close SaveChanges:=False
End Sub
NOTE: The keyword "Me" is shorthand for
referring to the current form from one of its own event procedures.
Coding the 'OK' Button
The OK button has to perform several tasks. Here are some
examples…
It must interpret the choice the user made from an Option Group
and convert it into a value suitable for entering at one of the
document's bookmarks. It does this by first declaring a string
(text) variable and using a series of "If statements" to place a
suitable value into the variable depending upon which of the option
buttons has a value of "true":
Dim strGreeting As String
If optGreeting1 = True Then strGreeting = "Yours sincerely"
If optGreeting2 = True Then strGreeting = "Yours faithfully"
If optGreeting3 = True Then strGreeting = "Kind regards"
If optGreeting4 = True Then strGreeting = "Live long and prosper"
Sometimes a simple value, such as one the choices offered by the
cboSenderAddress combo box needs to be translated into
something more elaborate, such as a multi-line address:
Dim strSenderAddress As String
If cboSenderAddress.Value = "London" Then
strSenderAddress = "Galactic Enterprises" _
& vbCrLf & "1001 Federation Plaza" _
& vbCrLf & "London" & vbCrLf & "Earth"
End If
NOTE: This section of code uses the underscore
character "_" to break lines of code to make them easier to
read, and the VBA keyword "vbCrLf" which represents a line
break in the resulting string.
Finally, the cmdOK_Click procedure must transfer the
values held in the variables above, and those entered into the other
textboxes and combo boxes, to the bookmarks in the letter document.
Each value is assigned to a bookmark with a line something like
this:
ActiveDocument.Bookmarks("Salutation").Range.Text = txtSalutation.Value
As there are a lot of these, a "With statement" can be used. The
completed procedure is shown below. Note also the use of
Application.ScreenUpdating to hide any on-screen changes until
the process finishes. The procedure finishes by closing the form.
Follow this link to see the code for the
cmdOK_Click procedure
[new window].
Code to Display the UserForm
The form needs to be displayed automatically when the user opens
a document based on the letter template. Find the ThisDocument
entry for your letter document in the Project Explorer pane of the
Visual Basic Editor (if you can't see it, double-click the
Microsoft Word Objects folder to display it) and double-click it
to open its code window. Use the drop-down lists at the top of the
code window to create a Document_New procedure. This event
occurs when a new document is created from a template. Enter a
line of code to open the form thus: Private Sub Document_New()
frmInterviewInvitation.Show
End Sub
Check the Code
The Visual Basic Editor will have warned you about most kinds of
typing errors, grammatical errors and omissions that you might have
made when entering your code. As a final check, open the Visual
Basic Editor's Debug menu and choose Compile Project.
This should alert you of any other things you might have forgotten,
such as undeclared variables. When you are satisfied that all is OK,
save the document.
Additional Features
This tool could be further enhanced by adding a validation
routine to check that all the required information had been
supplied; code to protect the document against further modification;
automatic printing of the completed document etc.
Save the Document as a Template
When you are satisfied with your document you must save it as a
Word Template. When you subsequently use a template you use
Word's File > New command to open a copy of the template file
rather than the file itself. This means that the original template
document remains unchanged and will always open looking the way you
intended.
Open the File menu and choose Save As to open the
Save As dialog. In the Save as type section choose
Document Template then enter a suitable name for your template
and click Save.
NOTE: When choosing a location for your template you
should bear in mind that when the user chooses File > New
there are taken to the default Templates folder. This is a good
place to save templates, as they are easily accessible from here.
You can store your templates in any location but you should remember
that they should always be opened for use using the File > New
command. Only use the File > Open command if you want to open
the template file itself for editing. You can also open a template
for use by locating its file with Windows Explorer and
double-clicking the file's icon.
Remember to test your template thoroughly and correct any errors
before unleashing it on any other users.
You can download a fully working copy of the completed Word
template from one of the links below. Right-click the link and
choose Save Target As...
|
InterviewLetter.dot
[43KB]
InterviewLetter.zip
[12KB]