|
The purpose of this Microsoft Word Help & Tips page is
to supplement my basic Create and Employ a UserForm.
Here I will show you several methods for displaying your UserForm data in
your document and for making information in your document interactive with controls
(i.e., text fields, listboxes, etc.) in the UserForm. |
|
In many instances users want to reopen a UserForm after the initial document
is prepared in order to update or modify information previously entered. If
the data in the document is not interactive with the UserForm then a blank
UserForm will be displayed and the user will have to manually re-enter the
information. |
|
First we will look at some basic UserForm text field data and how it is
gathered in the UserForm. The following illustration shows a
basic UserForm with several text fields.
Note the empty fields. This is how most basic UserForms will appear
when displayed. |
|
|
|
The user enters initial data into the UserForm and populates the document. |
|
|
|
Data from a UserForm can be displayed in the document in several different
ways. The following illustration shows examples using several of
these methods: |
|
|
|
The follow code was used to populate
the UserForm data into the document segment shown above. The code is
contained in a standard project module. |
|
|
Sub PopulateDocument(ByRef oUF As UserForm)
Dim oDoc As Word.Document
Set oDoc = ActiveDocument
If oDoc.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect
'Set the variable values
oDoc.Variables("varDemo1").Value =
oUF.txtVarDemo1.Text
oDoc.Variables("varDemo2").Value = oUF.txtVarDemo2.Text
'Call procedure to set the bookmark range
values. See extended notes.
SetBMRangeValues "bkmDemo1",
oUF.txtBkmDemo1.Text
SetBMRangeValues "bkmDemo2", oUF.txtBkmDemo2.Text
'Set cell range text
With ActiveDocument.Tables(1)
.Cell(1, 1).Range.Text = oUF.txtCellDemo1.Text
.Cell(1, 2).Range.Text = oUF.txtCellDemo2.Text
End With
'Set ContentControl range text
With ActiveDocument
.SelectContentControlsByTitle("ccDemo1").Item(1).Range.Text =
oUF.txtCCDemo1.Text
.SelectContentControlsByTitle("ccDemo2").Item(1).Range.Text =
oUF.txtCCDemo2.Text
End With
'Set FormField result
With oDoc
.FormFields("ffDemo1").Result = oUF.txtFFDemo1.Text
.FormFields("ffDemo2").Result = oUF.txtFFDemo2.Text
.Protect wdAllowOnlyFormFields, True
End With
'Update fields to show current docvariable
values.
FullDocumentUpdateFields '
code availabe in template download at end of this page.
End Sub |
|
I passed the UserForm as a argument to the PopulateDocument procedure shown
above using
the a command button click event in the UserForm. |
|
|
Private Sub cmdPopulateDoc_Click()
PopulateDocument Me
Me.Hide
End Sub |
|
When a user needs to display the UserForm again in the current document to
update or modify data, he or she will most likely want the existing data in
the document to be visible in the form. This can be accomplished using
the UserForm_Initialize event as follows: |
|
|
Private Sub UserForm_Initialize()
With Me
'Get the variable data
.txtVarDemo1.Text = ActiveDocument.Variables("varDemo1").Value
.txtVarDemo2.Text = ActiveDocument.Variables("varDemo2").Value
'Call Function to get the bookmarked data.
See extended notes.
.txtBkmDemo1.Text = GetBMRangeValue("bkmDemo1")
.txtBkmDemo2.Text = GetBMRangeValue("bkmDemo2")
'Get the cell text data
.txtCellDemo1.Text = Left(ActiveDocument.Tables(1).Cell(1,
1).Range.Text, _
Len(ActiveDocument.Tables(1).Cell(1, 1).Range.Text) - 2)
.txtCellDemo2.Text = Left(ActiveDocument.Tables(1).Cell(1,
2).Range.Text, _
Len(ActiveDocument.Tables(1).Cell(1, 2).Range.Text) - 2)
'Get the ContentControl data
.txtCCDemo1.Text =
ActiveDocument.SelectContentControlsByTitle("ccDemo1").Item(1).Range.Text
.txtCCDemo2.Text =
ActiveDocument.SelectContentControlsByTitle("ccDemo2").Item(1).Range.Text
'Get the form field result data
.txtFFDemo1.Text = ActiveDocument.FormFields("ffDemo1").Result
.txtFFDemo2.Text = ActiveDocument.FormFields("ffDemo2").Result
End With
End Sub |
|
Extended Notes - Bookmarks seem to be the document placeholder method of
choice for many. They can also be easily deleted or overwritten.
Especially if they are not displayed in your documents. Accordingly, I
recommend using a pair of separate procedures that checks and verifies the
presence of the bookmarks before attempting to read from or write to their
respective ranges. Of course the some of the other placeholders could
also be deleted. When you make your document design decisions you
might consider checking for their presence as well. |
|
|
Sub SetBMRangeValues(ByRef pBMName As String, pText As
String)
Dim oRng As Word.Range
If ActiveDocument.Bookmarks.Exists(pBMName) Then
Set oRng = ActiveDocument.Bookmarks(pBMName).Range
oRng.Text = pText
ActiveDocument.Bookmarks.Add pBMName, oRng
Else
MsgBox "An error occurred while processing your document." _
& vbCr & "The bookmark " & Chr(34) + pBMName + Chr(34) & "
does not exist.", _
vbInformation, "Missing Bookmark"
End If
End Sub
Function GetBMRangeValue(pBMName As String) As String
If ActiveDocument.Bookmarks.Exists(pBMName) Then
GetBMRangeValue =
ActiveDocument.Bookmarks(pBMName).Range.Text
Else
MsgBox "An error occurred while retrieving data from your
document." _
& vbCr & "The bookmark " & Chr(34) + pBMName + Chr(34) & "
does not exist.", _
vbInformation, "Missing Bookmark"
End If
End Function |
|
With several of the methods shown data can be updated or modified in the
document and those changes can be reflected in the UserForm. For
example lets change the table cell data in our document example and then
redisplay the UserForm. |
|
|
|
Of the five methods shown only four are truly interactive in this manner
(i.e., changes made in the document are reflected in the UserForm).
While the DocVariable field is a very robust method for data display in a
document, manual changes to the field text do not result in an actual change
in the variable value and should be avoided. |
|
Ok that was the easy part. Now lets look at the more challenging
aspects of making UserForm Listboxes, Comboboxes, Checkboxes, and Option
Button interactive with document data. In the remaining examples, I
will be using document bookmarks (Method 2 shown above) to display the UserForm data. |
|
Consider the following UserForm that has been filled out by the user: |
|
|
|
When populated, the document will appear as follows: |
|
|
|
The follow code was used to populate
the UserForm data into the document segment shown above: |
|
|
Sub PopulateDocumentII(ByRef oUF As UserForm)
Dim oDoc As Word.Document
Dim pStrEdu As String
Set oDoc = ActiveDocument
If oDoc.ProtectionType <> wdNoProtection Then ActiveDocument.Unprotect
'Set the Listbox bookmark data
SetBMRangeValues "ListboxValueBM",
oUF.listboxDemo1.Value
'Set the Combobox bookmark data
SetBMRangeValues "ComboBoxValueBM",
oUF.comboboxDemo1.Value
With oUF
'Build a string based on
checkboxes that are checked
If .chkHS Then pStrEdu = pStrEdu
+ "_High School"
If .chkBach Then pStrEdu = pStrEdu + "_Bachelors"
If .chkMasters Then pStrEdu = pStrEdu + "_Masters"
If .chkPhD Then pStrEdu = pStrEdu + "_PhD"
If .chkRS Then pStrEdu = pStrEdu + "_Reform School"
'Clean up and format the
string
pStrEdu = Replace(pStrEdu, "_",
", ")
If Len(pStrEdu) > 2 Then pStrEdu = Right(pStrEdu, Len(pStrEdu)
- 2)
On Error Resume Next
pStrEdu = Left(pStrEdu, InStrRev(pStrEdu, ",") - 1) & " and"
& Mid(pStrEdu, InStrRev(pStrEdu, ",") + 1)
On Error GoTo 0
'Set the eductation bookmark
data
SetBMRangeValues "bkmEducation",
pStrEdu
'Set the color bookmark data
If .optRed Then
SetBMRangeValues "bkmColor", "Red"
ElseIf .optBlue Then
SetBMRangeValues "bkmColor", "Blue"
ElseIf .optGreen Then
SetBMRangeValues "bkmColor", "Green"
Else
SetBMRangeValues "bkmColor", "User
did not respond"
End If
End With
End Sub
The UserForm was passed to this procedure by the command button click event
in the UserForm. The SetBMRangeValues procedure is the same as that
shown previously. |
|
This UserForm is made interactive with the document using the follow code in
the UserForm_Initialize event:
The UserForm Initialize event is also used to populate the ListBox and
ComboBox list member values. |
|
|
Private Sub UserForm_Initialize()
Dim i As Long
Dim pStrData As String
With Me
'Populate the ListBox
With .listboxDemo1
.AddItem "13"
.AddItem "48"
.AddItem "50"
.AddItem "65"
End With
'Populate the ComboBox
With .comboboxDemo1
.AddItem "Chevrolet"
.AddItem "Ford"
.AddItem "Honda"
.AddItem "Toyota"
End With
'Call Function and set the ListBox value from
data in document
pStrData = GetBMRangeValue("ListboxValueBM")
For i = 0 To
.listboxDemo1.ListCount - 1
If .listboxDemo1.List(i) =
pStrData Then
.listboxDemo1.ListIndex = i
Exit For
End If
Next i
'Call Function and set the ComboBox value from
data in the document
.comboboxDemo1.Value =
GetBMRangeValue("ComboboxValueBM")
'Set the checkbox values from
data in the document
pStrData =
GetBMRangeValue("bkmEducation")
If InStr(pStrData, "High School") > 0 Then Me.chkHS.Value = 1
If InStr(pStrData, "Bachelors") > 0 Then Me.chkBach.Value = 1
If InStr(pStrData, "Masters") > 0 Then Me.chkMasters.Value =
1
If InStr(pStrData, "PhD") > 0 Then Me.chkPhD.Value = 1
If InStr(pStrData, "Reform School") > 0 Then Me.chkRS.Value =
1
'Set the option button
values from data in the document
Select Case
GetBMRangeValue("bkmColor")
Case "Red"
Me.optRed.Value = 1
Case "Blue"
Me.optBlue.Value = 1
Case "Green"
Me.optGreen.Value = 1
End Select
End With
End Sub
For more information on populating ListBoxes see my:
Populate UserForm ListBox |
|
In the preceding example, we used plain text to represent the user checkbox
selection. There is a method that you can use to make true interactive
checkboxes. See:
Interactive UserForm Checkboxes |
|
You can download a document contain the UserForms and code used to produce
this Microsoft Word Help & Tips page
here. |
|
|
Looking for something else?
|
