Resting Anchor

The Anchorage

Personal website of Gregory K. Maxey, Commander USN (Retired)

Address Basic Letter With UserForm
(A Microsoft Word Tip page by Gregory K. Maxey)

DISCLAIMER/TERMS OF USE

The information, illustrations and code contained in my "Microsoft Word Tips" are provided free and without risk or obligation.

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

However, the work is mine. If you use it for commercial purposes or benefit from my efforts through income earned or time saved then a donation, however small, will help to ensure the continued availability of this resource.

If you would like to donate, please use the appropriate donate button to access PayPal. Thank you!


The purpose of this Microsoft Word Tips & Microsoft Word Help page is to provide an intermediate level demonstration of employing a userform to address a basic letter. You can use this information to create your own personal letter template and simplify preparing your PC generated correspondence to your friends, clients, or customers. The userform described here will automatically date the correspondence, it contains a method for building the composite address block from individual userform address lines, and it will assist with preparing the salutation line. You will also learn a few basic techniques for validate the userform entries and how to populating a simple combo box.  There's lots of neat stuff to learn about so lets get started!!

First lets review the template.  The template contains the boiler plate text of your basic letter and defines the "placeholders" (or containers for receiving/display data inputted in the userform).  The template also contains the VBA project for the userform object and related code.  For this project I've employed a variety of range anchors in order to demonstrate methods for displaying data in the various containers. The basic template is shown below:

Basic ltr

As you can see, the template defines the layout of the letter, information about the sender, and placeholders to receive and display data from the userform. I've left the field code for the DocVariable field displayed for illustration.

The userform is shown below.  It contains identification labels, text fields, a combobox, and a command button.

Basi ltr userform

Site Note icon For more information of userforms, see the userform FAQ Index at the Word MVP website.  There you will find a wealth of tips including steps to create the most basic userform. Also visit my own Validate Userform Text Entry and Create and Employ a Userform.   

An AutoNew procedure store in a standard module of the template VBA project is used to initiate and display the userform each time a new document is created using the template. 

VBA Code:
Sub AutoNew()
Dim oFrm As myFrm
Set oFrm = New myFrm
oFrm.Show
Set myFrm = Nothing
End Sub

The userform assists in completing the salutation by suggesting "Dear" and the first word entered in the Client name field. This is accomplished automatically when the user exits the Client Name field.

Fill in assist

The userform contains a combobox that lists the USPS abbreviation for each of the fifty states.

Combo box

Selecting the "Create Letter" command button transfers the userform data to the placeholders  in the document. The userform automatically builds the letter address field from the multiple optional address fields in the form.

basic completed letter

The code used to analyze the data entry and populate the document placeholders is contained in a form module the template VBA project. By studying studying and adapting the code to meet your specific need, you should be able to create and customize your own basic letter forms.  Good luck!!

VBA Code:
Option Explicit
Private Sub cmdCreate_Click()
'Declare variables
Dim oBMs As Bookmarks
Dim oVar As Variable
Dim oCC As ContentControl
Dim oRng As Word.Range
Dim strAddress As String
Set oBMs = ActiveDocument.Bookmarks
  'Validate key entries
  If Me.cbStates.ListIndex = -1 Then
    MsgBox "You must scroll to and " & Chr(34) & "select" & Chr(34) & " the appropriate state"
    Exit Sub
  End If
  With Me.txtDate
    If Not IsDate(.Text) Then
      MsgBox "Invalid date entry. Enter the letter date in a valid date format."
      .SetFocus
      .SelStart = 0
      .SelLength = Len(.Text)
      Exit Sub
    Else
      'Write the date to the document bookmark range.
      Set oRng = oBMs("Date").Range
      'This destroys the bookmark.
      oRng.Text = .Text
      'This recreates the bookmark.
      oBMs.Add "Date", oRng
    End If
  End With
  'Create the "Client" variable
  ActiveDocument.Variables("Client").Value = Me.txtName.Text
  'Build the multi-line address string
  If Len(Me.txtAddr1.Text) > 0 Then
    strAddress = Me.txtAddr1.Text & vbCr
  End If
  If Len(Me.txtAddr2.Text) > 0 Then
    strAddress = strAddress & Me.txtAddr2.Text & vbCr
  End If
  If Len(Me.txtAddr3.Text) > 0 Then
    strAddress = strAddress & Me.txtAddr3.Text & vbCr
  End If
  If Len(Me.txtCity.Text) > 0 Then
    Me.txtCity.Text = Me.txtCity.Text & ","
  End If
  strAddress = strAddress & Me.txtCity.Text & " " & Me.cbStates.Value & " " & Me.txtZip.Text
  'Write the address to the CC titled "Client Address"
  ActiveDocument.SelectContentControlsByTitle("Client Address").Item(1).Range.Text = strAddress
  'Write the subject to the CC tagged "Subject'
  ActiveDocument.SelectContentControlsByTag("Subject").Item(1).Range.Text = Me.txtSubj.Text
  'Write the salutation to the document bookmark range.
  Set oRng = oBMs("Salutation").Range
  oRng.Text = txtSalutation.Text
  oBMs.Add "Salutation", oRng
  'Update fields and toggle codes.
  With ActiveDocument.Fields
    .ToggleShowCodes
    .Update
  End With
Unload Me
lbl_Exit:
Exit Sub
End Sub
Private Sub txtName_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
  i = InStr(Me.txtName, " ")
  On Error Resume Next
  Me.txtSalutation = "Dear " & Left(txtName, i - 1) & ","
  On Error GoTo 0
lbl_Exit:
Exit Sub
End Sub
Private Sub UserForm_Initialize()
Dim arrStateList() As String
  'Set current date
  Me.txtDate = Format(Now, "MMMM, dd yyyy")
  With Me.txtDate
    .SetFocus
    .SelStart = 0
    .SelLength = Len(.Text)
  End With
  'Define combobox list members in an array.
  arrStateList = Split(" AL AK AS AZ AR CA CO CT DE DC FM FL GA GU " _
                 & "HI ID IL IN IA KS KY LA ME MH MD MA MI MN MS " _
                 & "MO MT NE NV NH NJ NM NY NC ND MP OH OK OR PW " _
                 & "PA PR RI SC SD TN TX UT VT VI VA WA WV WI WY")
  'Populate combobox list.
  Me.cbStates.List = arrStateList
lbl_Exit:
Exit Sub
End Sub

That's it! I hope you have found this tips page useful and informative.  You can download the template used in this tips page discussion here: Basic Letter Userform

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo