Most businesses use form letters or standard documents, in which a few details need to be changed each time the document is created. The best way to deal with this is to save the document as a template. You can add form fields for the variable content to make the job of completing the document easier. But for the greatest control and ease of use, you can't beat the versatility of a VBA UserForm. A VBA UserForm is a custom dialog box, to which you can add all the features you would normally see in a regular Windows dialog box like text boxes, combo boxes, option groups, check boxes and more.
This three part tutorial shows you how to build an automatic document template for Microsoft Word. The template uses VBA to display a dialog box into which the user enters the necessary information. At the click of a button the information is transferred to the document and it's ready to print.
The first part of the tutorial deals with preparing the standard document. In the second part we design and build a VBA UserForm, and in part three we write the VBA code that powers the form.
Your project might be a standard document such as a lease or contract, or a form letter such as a job offer or interview invitation. In this tutorial I use the example of an interview invitation.
The first task is to decide which parts of the document change each time, and which remain the same. If the document is a letter, for example, write it as if you were addressing it directly to an individual but, instead of entering any personal information, insert a bookmark.
For example, instead of writing "Dear Mr Green" you should write "Dear " and then enter a bookmark to define the place where the salutation "Mr Green" will be inserted from the UserForm. Before inserting your bookmarks you might find it useful to go to Tools > Options > View and tick the Bookmarks option. This ensures that you can see the position of a bookmark after you have inserted it.
To insert a bookmark place your cursor where you want your inserted text to appear and go to Insert > Bookmark. This displays the Bookmarks dialog box. Type a name for your bookmark and click Add. Each bookmark in a document must have a unique name, and it makes sense to keep the names relevant and concise.
If you have opted to view bookmarks, as suggested above, you will see a grey marker denoting the position of the bookmark. If not, the bookmark will be invisible. This feature can be switched on and off at any time and does not affect the performance of the bookmark. You may prefer to have the bookmarks visible whilst you are creating the template, but hide them when you are using the document.
You don't need to leave any additional space for the text that will be inserted into your bookmark as this will happen automatically. If your inserted text will appear in the middle of a sentence, then your bookmark should have a space (or other suitable punctuation) on either side. If it is at the end of a sentence it should be followed immediately by the full-stop (period).
As you add bookmarks to your document they are listed in the Bookmarks dialog. This dialog also gives you the facility to delete bookmarks from your document, or to quickly go to a bookmark's position...
Bookmarks can be inserted as you type or you may prefer to type a sample document first, and then edit it to remove personalized text replacing each piece with a bookmark.
When designing out your document remember that, whilst the bookmark marker occupies only a small space, your inserted text will probably require more. This might affect the document's layout so take this into consideration if the layout is particularly important. Here's how the sample document looks after all the bookmarks have been inserted...
Several kinds of self-updating information can be added to your document with the aid of Word fields. You can see that the letter contains a date. Don't type a date! If you do that it will have to be changed each time you use the template. Instead insert the date as a Word field. Place your cursor where you want the date to appear and go to Insert > Date and Time and choose the format you want. Make sure that the Update Automatically option is ticked and click OK to place the field in your document. This field will then display the current date whenever the document is opened (i.e. each time you create a new document using your new template).
Now is a good time to save your document. You can save it as a regular Word document (*.doc) and save it as a template later when all the work is finished.
In the next step of the tutorial we build the VBA UserForm that
will collect information from the user for insertion into the
This project will make use of a custom-built dialog box to collect information from the user. Such a custom dialog box, called a UserForm, is created and powered by VBA. This second part of the tutorial deals with building a UserForm to collect all the necessary information needed to complete the document created in the first part of the tutorial.
The document, the UserForm and its accompanying VBA code are contained within the same file. The work of building and coding the UserForm is done using in Word's Visual Basic Editor.
For this step you need your document open in Word although the job of building the UserForm will be done in the Visual Basic Editor. Open the Visual Basic Editor by going to Tools > Macro > Visual Basic Editor or use the keyboard shortcut Alt+F11. Unless you have previously made changes here, the Visual Basic Editor opens with an empty code window, with Project Explorer and Properties panes displayed on the left of the main window. If they are not displayed, you can switch on the Project Explorer and Properties panes from the View menu.
The Project Explorer pane is used to navigate through the various objects that you will be working with. It works a bit like Windows Explorer with items grouped into a hierarchy of folders. Here you can see a reference to the documents currently open: the Normal template (which opens and is hidden whenever Word opens) and the letter you are working on. In this example the letter has been given the file name InterviewLetter.doc. Any other open documents will be listed here too.
In the Project Explorer pane click on the name of your letter document to select it and go to Insert > UserForm. This opens a blank UserForm in the code window, and an entry for the UserForm in the Project Explorer pane, and displays the Toolbox...
You will use the blank UserForm to build a custom dialog box to help your user complete the letter automatically. The Toolbox contains all the objects you might need to put on your form. If it does not appear either click the Toolbox button on the Visual Basic Editor toolbar or go to View > Toolbox.
In the Project Explorer click UserForm1 to select it and then move to the Properties Window below. The Properties Window shows a list of the properties of the currently selected object, in this case the UserForm itself.
Change the Name property to something more meaningful (object names must not contain spaces and it is customary to prefix form names with the code frm). This example uses the name frmInterviewInvitation.
Change the Caption property to what you would like to see on the form's header bar (the coloured bar at the top of each dialog box and window). This is what the user will see on the dialog box so choose something suitable. This example uses the caption Invitation to Interview.
When you make these changes you will see corresponding changes to the form itself and to the entries in the Project Explorer pane...
NOTE: You can save your work at any time by going to File > Save or clicking the Save button in either the Visual Basic Editor window or in the Word document window. The UserForm is saved as part of your letter document so, whilst you are still developing your new template, remember to save your changes whenever you close the document.
The next step is to add objects, called Controls to the form. Controls can take the form of a TextBox (used to enter free text), a ComboBox (which offers the user a drop-down list of options, an Option Group (where the user can make a choice by clicking Radio Buttons), a Check Box (where the user places a tick to select an item) or a Command Button (that the user clicks to give an instruction).
There are several other controls in the Visual Basic Editor toolbox and more can be added (such as a Calendar) in the form of ActiveX Controls.
If you require only text input from the user, a TextBox control will suffice. Click the TextBox tool on the toolbox then click somewhere on the UserForm. A selected textbox control appears on the UserForm. You can move the control by dragging one of its edges, or change its size by dragging one of the white rectangles around its border. Resize the textbox and drag it to a suitable position on the UserForm, leaving enough room for a label if you plan to add one.
The Visual Basic Editor automatically assigns a name to each control you create (e.g. TextBox1). You can make the job of code writing much easier by giving each control a meaningful name. It would be sensible to give the textbox the same name as the bookmark it is related to. It is also conventional, but not essential, to prefix the name with a code that indicates the type of control. In this example, the first textbox will supply text to the RecipientName bookmark so it has been given the name txtRecipientName.
With the textbox selected use the Properties pane to change its Name property to something more relevant.
Most controls need a label. Click the Label tool on the toolbox and click on the UserForm to create a label. The label's name is not really important in this instance but its content is. You can add content to a label either by clicking inside the label and typing directly into it, or by changing the label's Caption property in the Properties pane.
NOTE: Avoid the temptation to double-click on an object on the UserForm. The Visual Basic Editor interprets this as an instruction to open a code event procedure for the object - handy when you are writing the form's code but you don't need it right now. If you do it accidentally just press Ctrl+Tab until you return to the form design window (or use the Window menu to find it), or double-click the form's name in the Project Explorer pane.
The label can be resized in the same way as the textbox and can contain large amounts of text if necessary. Text will wrap and flow within the label as its shape and size are changed. Make sure that your label is big enough to display all your text. A quick way to resize a label to fit its caption exactly is to double-click one of the resizing handles.
Move the label to its correct position and deselect it so you can see how it looks. You can check its final appearance by pressing F5 to preview the form.
By default a textbox permits only one line of text. This is fine for most cases (such as the Recipient Name) but sometimes you want the user to be able to enter several lines, as will be the case with the Recipient Address textbox. The control is drawn in the same way but this time set the MultiLine property to True. Normally you would also want to provide a scrollbar so set the ScrollBars property to 2-fmScrollBarsVertical.
When entering data into this type of textbox the user should press Ctrl+Enter or Shift+Enter to create a new line. Another option is to set the EnterKeyBehaviour property of the textbox to True. When you do this the user can create a new line in the textbox just by pressing the Enter key.
NOTE: You can offer users hints by making use of a control's ControlTipText property. Enter a short message that you would like to appear as a tool tip when the user pauses their mouse over the control...
A combo box provides a simple way to offer a familiar drop-down list of entries from which the user can make a choice. Draw a combo box in the same way as you would a textbox, using the ComboBox tool.
When using a combo box you can choose whether or not to allow the user to make any entry, using the control like a textbox, or to limit their entries to those on your list. If you want to limit the user to the list you must set the MatchRequired property of the combo box to True.
Use the Name property to give your combo box a meaningful name. It is customary to use the prefix cbo when naming combo boxes. This one is called cboInterviewLocation.
NOTE: Most controls have many different property options available. The most commonly used properties are set as defaults. To find out more about any property, click on the property in the Properties pane and press the F1 key.
The task of creating the list itself is performed by a code procedure when the form opens. That will be dealt with later.
Combo boxes can offer a large list of choices, but when you want to offer the user just a few choices sometimes an option group is more appropriate. An Option Group is a collection of Option Buttons (sometimes called Radio Buttons) placed within a Frame (in fact option buttons on a VBA UserForm do not need to be placed within a frame to work as a group, but it helps the user to understand that they work together).
When building an option group you can choose whether to use Option Buttons, Checkboxes or Toggle Buttons all of which will work the same way. Most commonly option buttons are used. When working as a group the user may only select one option. If they select another the previous choice is deselected.
In this example an option group is used to offer the user a choice of greetings with which to end the letter.
First, draw a frame on the UserForm. Don't worry too much about its exact size and shape - you can adjust that later. Use its Caption property to add a label to the top of the frame.
Now use the OptionButton tool to draw the required number of option buttons inside the frame, filling the descriptive label of each one by entering text into the Caption property or by typing directly into the label.
Use the Name property to give each option button a meaningful name. In my example I have called them optGreeting1 to optGreeting4. If you have chosen to place your option buttons inside a frame they will automatically behave together as a group. If you prefer not to use a frame - or wish to have more than one group of buttons - make use of the GroupName property of each option button. Controls with the same GroupName will work together as a group even if they are not enclosed inside a frame.
Finally, the form needs some buttons to initiate the various tasks the form has to perform. First, an OK button that will result in the data being transferred to the standard letter; a Cancel button to close the form and throw away the letter if the user changes their mind; and (optionally) a Clear Form button to reset the form to its original values if the user wants to start again.
In VBA these are known as Command buttons. Like the other controls they can be drawn on the form using the toolbox Command Button tool.
Use the Properties pane to give the buttons meaningful names and captions. The convention is to prefix the name of a command button with cmd (e.g. cmdClear, cmdOK and cmdCancel).
Three additional properties are useful when creating command buttons:
The Accelerator property allows you to specify a letter that the user can type as an alternative to clicking the button. This letter should be one of those included in the button's caption. That letter then appears underlined on the caption. To assign an accelerator to a different sort of control, such as a combo box or text box, set the Accelerator property of its label.
The Default property, if set to True, causes that button to be 'clicked' if the user presses the Enter key on their keyboard. This property should only be set on one of the buttons, and would normally be applied to the OK button. Whilst generally useful, you might want to omit this feature if you think the user is likely to press Enter before they have completed the form as it might result in the form closing prematurely. The 'default' button also remains highlighted to indicate its purpose unless the user tabs to a different button.
The Cancel property, if set to True, has the effect of 'clicking' the button to which it is applied when the user presses the Escape (or Esc) key on their keyboard. This feature could usefully be applied to the Cancel button.
That completes the design of the form. Before proceeding any further check its appearance to the user by selecting the form and pressing the F5 key. You may want to alter the form's layout or size before adding the VBA code (although the form's design can be modified at any time).
NOTE: When arranging controls on a form, you can move several controls together, retaining their relative positions, by performing a multiple select. The easiest way to do this is to use the mouse to draw a selection rectangle enclosing the controls you want to select. When you release the mouse all the enclosed controls are selected and can be moved as one.
|> > >|
A final but important step in setting up the form is to check the Tab Order. This is the order in which the various controls will be visited when a user moves around the form by pressing their Tab key. An illogical order can be extremely frustrating for the user. Go to View > Tab Order to display a dialog box listing all the controls on the form in their current order. Use the buttons to rearrange the order if necessary. You can ignore the Label controls because, although they are listed here, they will be ignored by the tab order.
The completed form is shown below:
In the next step of the tutorial we write the VBA code that will
power the form.
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.
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
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
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.
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.
Here is the complete code listing for the cmdOK_Click procedure:
Private Sub cmdOK_Click() Dim strGreeting As String Dim strSenderAddress 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" If cboSenderAddress.Value = "London" Then strSenderAddress = "Galactic Enterprises" _ & vbCrLf & "1001 Federation Plaza" _ & vbCrLf & "London" & vbCrLf & "Earth" End If If cboSenderAddress.Value = "Jupiter Station" Then strSenderAddress = "Galactic Enterprises" _ & vbCrLf & "555 Upper Commerce Ring North" _ & vbCrLf & "Jupiter Station" End If If cboSenderAddress.Value = "Deep Space 9" Then strSenderAddress = "Galactic Enterprises" _ & vbCrLf & "140 Main Concourse West" _ & vbCrLf & "UFP Deep Space 9" End If Application.ScreenUpdating = False With ActiveDocument .Bookmarks("RecipientName").Range.Text = txtRecipientName.Value .Bookmarks("RecipientAddress").Range.Text = txtRecipientAddress.Value .Bookmarks("Salutation").Range.Text = txtSalutation.Value .Bookmarks("Position").Range.Text = txtPosition.Value .Bookmarks("InterviewLocation").Range.Text = cboInterviewLocation.Value .Bookmarks("InterviewDay").Range.Text = cboInterviewDay.Value .Bookmarks("InterviewDate").Range.Text = txtInterviewDate.Value .Bookmarks("InterviewTime").Range.Text = txtInterviewTime.Value .Bookmarks("InterviewDuration").Range.Text = cboInterviewDuration.Value .Bookmarks("Greeting").Range.Text = strGreeting .Bookmarks("SenderName").Range.Text = txtSenderName.Value .Bookmarks("SenderPosition").Range.Text = txtSenderPosition.Value .Bookmarks("SenderAddress").Range.Text = strSenderAddress End With Application.ScreenUpdating = True Unload Me End Sub
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
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.
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.
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.
©2002 Martin Green - www.fontstuff.com - email@example.com - All rights reserved