|
This Microsoft Word Help and Tips Page will show you how you can put a UserForm
to work and simplify preparing your PC generated correspondence to your friends,
clients, or customers. It automatically dates the correspondence, contains
a method for building the recipients address block from individual UserForm
address lines, and assists with preparing the salutation line. You will
also see a method for validating a date entry and populating a ListBox.
See Validate UserForm Text Entry
on this website for tips on how to prepare and use a basic UserForm.
The UserForm we will be using is shown below. The form is a part of the
Word template used to create each new letter. |
|
|
|
The next illustration shows the default document content of the letterhead
template. The template contains bookmarks identifying the location for the
"Date," "Addressee," "Address," and "Salutation."
Note: The dimmed grey brackets indicate the bookmark
boundaries. |
|
|
|
The UserForm is automatically displayed each time a new document is created
using the letterhead template. This is accomplished using an AutoNew macro
in the template. 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. |
|
|
|
The UserForm contains a ListBox that lists the USPS abbreviation for each of the
fifty states.
Note: The user must scroll to and "select" the
appropriate ListBox entry or type the entry in the ListBox field. |
|
|
|
Selecting the "Fill Form" command button transfers the UserForm
data to the bookmarked locations in the document. The UserForm
automatically builds the letter address field from the multiple optional address
fields in the form. |
|
|
|
The UserForm code for this project is shown below: |
|
|
Option Explicit
Private Sub CommandButton1_Click()
Dim oBMs As Bookmarks
Dim oRng As Word.Range
Dim pAddress As String
Set oBMs = ActiveDocument.Bookmarks
If Me.ListBox1.ListIndex = -1 Then
MsgBox "You must scroll to and " & Chr(34) & "select" &
Chr(34) & " the appropriate state"
Exit Sub
End If
With Me.TextBox1
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
Set oRng = oBMs("Date").Range
oRng.Text = .Text
oBMs.Add "Date", oRng
End If
End With
Set oRng = oBMs("Addressee").Range
oRng.Text = Me.TextBox2
oBMs.Add "Addressee", oRng
If Len(Me.TextBox3.Text) > 0 Then
pAddress = Me.TextBox3.Text & vbCr
End If
If Len(Me.TextBox4.Text) > 0 Then
pAddress = pAddress & Me.TextBox4.Text & vbCr
End If
If Len(Me.TextBox5.Text) > 0 Then
pAddress = pAddress & Me.TextBox5.Text & vbCr
End If
If Len(Me.TextBox6.Text) > 0 Then
Me.TextBox6.Text = Me.TextBox6.Text & ","
End If
pAddress = pAddress & Me.TextBox6.Text & " " & Me.ListBox1.Value & " " &
Me.TextBox7.Text
Set oRng = oBMs("Address").Range
oRng.Text = pAddress
oBMs.Add "Address", oRng
Set oRng = oBMs("Salutation").Range
oRng.Text = Me.TextBox8.Text
oBMs.Add "Salutation", oRng
Unload Me
End Sub
Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Long
i = InStr(Me.TextBox2, " ")
On Error Resume Next
Me.TextBox8 = "Dear " & Left(TextBox2, i - 1) & ","
On Error GoTo 0
End Sub
Private Sub UserForm_Initialize()
Dim aStateList() As String
Me.TextBox1 = Format(Now, "MMMM, dd yyyy")
With Me.TextBox1
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End With
aStateList = 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")
Me.ListBox1.List = aStateList
End Sub
|
|
You can download a the template used for creating this tips
page at: Template |
