10 steps to creating a Word userform for addressing letters

Userforms are a great way to solicit input. For instance, you can map a few bookmarks via a bit of VBA code, and a quick click can generate the address elements for a letter. By following these 10 steps, you can create a userform template that solicits address elements from a user.

There’s a lot going on, but the important part of this technique is mapping the userform controls to the bookmarks in the template. I used this method to generate address elements in a simple letter document (to simplify the example); you can use the same process for more complicated data-capturing tasks.

Note: You can download the sample template for this technique.

1: Begin with a basic design

Your initial design should include the appropriate control type for each address element and a name for each control. Table A identifies the control type, the control name, and the bookmark name for each address element. The command button will copy the input values from the userform to the letter. The bmAddress2 bookmark is listed multiple times because we’ll combine the city, state, and ZIP code into one line. It’s okay if things change as you progress. This is just something to get you started.

Table A

We’ll use these address elements for the form.

2: Create the userform

Using Figure A as a guide, you’ll add the controls listed in Table A to a userform. First, open the VB Editor by pressing [Alt]+[F11]. Then, choose UserForm from the Insert menu to display a blank userform and the Toolbox. If you don’t see the Toolbox, choose Toolbox from the View menu. If it disappears at any time, just click the userform.

Figure A

Add these controls to the blank userform.

After adding the controls, name them via the Properties Window. If you don’t see that window, press [F4]. Set the Address text box’s MultiLine property to True. (To arrange the controls, select them and choose the appropriate options from the Format menu.)

3: Populate the controls

We’ll use VBA to populate the date and state controls when you run the form. To open the form’s module, double-click the userform. Then, enter the following code:

Private Sub UserForm_Initialize()

'Populate cboState in ufAddressLetter.

Me.cboState.List = Split("AL AK AZ AR CA CO CT DE DC FL GA" _

 & "HI ID IL IN IA KS KY LA ME MD MA MI MN MS MO MT NE NV NH" _

 & "NJ NM NY NC ND OH OK OR PA RI SC SD TN TX UT VT VA WA" _

 & "WV WI WY")

'Populate txtDate in ufAddressLetter. You can overwrite this value.

Me.txtDate.Value = Format(Now, "mmmm dd, yyyy")

End Sub

Double-click ufAddressLetter in the Project Window to return to the form. To see the code at work, press [F5] to run the userform. Doing so will populate the two controls, as shown in Figure B.

Figure B

The userform’s Initialize event populates two controls.

4: Automate the salutation

Return to the module and add the following code, which will automatically fill in the salutation control using the first name entered in txtName:

Private Sub txtName_Exit(ByVal Cancel As MSForms.ReturnBoolean)

'Automatic fill-in for salutation control.

On Error Resume Next

Me.txtSalutation = "Dear " & Left(txtName, InStr(Me.txtName, " ") - 1) & ":"

On Error GoTo 0

End Sub

Use the VBE’s Object and Procedure controls to enter the procedure’s stub (the Sub and End Sub lines) to make sure the referenced arguments are correct.

5: Add the code that generates the letter

Using Table A, you’re ready to map the userform controls to the code that will transfer input values from the userform to the letter. (The bookmarks don’t exist yet, but that’s okay.) Double-click the userform to return to its module and then enter the following code:

Private Sub cmdAddLetter_Click()

'Copy letter elements to letter template from userform

Dim bmks As Bookmarks

Dim bmRange As Range

'Pass the userform values to the document's bookmarks.

Set bmks = ActiveDocument.Bookmarks

Set bmRange = ActiveDocument.Bookmarks("bmDate").Range

bmRange.Text = Me.txtDate.Value

Set bmRange = ActiveDocument.Bookmarks("bmName").Range

bmRange.Text = Me.txtName.Value

Set bmRange = ActiveDocument.Bookmarks("bmAddress").Range

bmRange.Text = Me.txtAddress.Value

Set bmRange = ActiveDocument.Bookmarks("bmAddress2").Range

bmRange.Text = Me.txtCity.Value & ", " _

 & Me.cboState.Value & " " _

 & Me.txtZip.Value

Set bmRange = ActiveDocument.Bookmarks("bmSalutation").Range

bmRange.Text = Me.txtSalutation.Value

Me.Hide

End Sub

6: Add the code that runs the userform

You’ll need a macro to display the userform. Open the ThisDocument module (from the Project Explorer) and enter this short procedure:

Sub RunUserForm()

'Run sfAddressLabel

Dim frm As New ufAddressLetter

frm.Show

End Sub

7: Automate the userform

There’s one last bit of code to add. While still in the ThisDocument module, enter the following code:

Sub AutoNew()

'Run ufAddressLetter when file opens.

Call RunUserForm

End Sub

Word will run this code to display the userform each time you open the file.

8: Insert the bookmarks

Now that the userform and its code are in place, return to the blank Word document so you can insert the bookmarks. Displaying bookmarks will make this step easier:

1. Click the File tab and then click Options (under Help). In Word 2007, click the Office button and then choose Word Options. In Word 2003, choose Options from the Tools menu, click View, and skip to step 3.
2. Click Advanced in the left pane.
3. Check Show Bookmarks in the Show Document Content section. (In Word 2003, it’s the Show section.)
   Click OK.

Figure C

Check the Show Bookmarks option.

Refer to Table A for the bookmark names and use Figure D as a placement guide to insert each bookmark as follows:

1. Click the Insert tab. In Word 2003, choose Bookmark from the Insert menu and skip to step 3.
2. Click Bookmark in the Links group.
3. Enter the name of your bookmark. For instance, your first bookmark will be bmDate.
4. Click Add.

Now, insert the remaining bookmarks.

Figure D

Displaying the bookmark symbols makes it easier to insert multiple bookmarks.

9: Save as a template

Most likely, you’re not going to all this trouble for a single document, so you’ll want to save your work as a template. Do so as follows:

1. Click the File tab (Office button in 2007) and click Save As. In Word 2003, choose Save As from the File menu and skip to step 3.
2. Click Trusted Templates in the Places bar. (You must store your template in a trusted location for the AutoNew() macro to work. Otherwise, you’ll have to reset your macro security settings.)
3. Enter a name for the file. (In Word 2003, alter the folder location, as necessary.)
4. From the Save As Type drop-down, choose Word Macro-Enabled Template (.dotm). In Word 2003, choose Document Template (.dot).
5. Click Save.

10: Open the template

To use ufAddressLetter to generate the address for a letter, create a new document based on the template as follows:

1. Click the File tab (Office tab in 2007) and choose New. In Word 2003, choose New from the File menu.
2. In the Available Templates section, click My Templates. In Word 2003, choose a template from the New Document pane (to the right).
3. Choose the template and click OK.

Put the userform to work

When first open, the userform defaults to the current date. When you press [Tab] or [Enter] after entering a name, the form will fill in the salutation. You can change both if you like. When using the multiline feature to enter an address with more than one line, press [Ctrl]+[Enter] to force the cursor to the next line in the control. Figure E shows the form filled in. Click the Address Letter command button to generate the letter, shown in Figure F, using the input values.

Figure E

Fill in the userform.

Figure F

Click the command button to generate the address elements of your new letter.

Extra considerations

The code has no data validation or error handling. There’s also no code for clearing the bookmarks. To create a new letter, in this case, you’d just open a new template document. You can re-execute RunUserForm by using the Macros option in the Code group on the Developer tab. When applying this technique to more complex forms and custom documents, you can handle these things differently.