Resting Anchor

The Anchorage

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

Interactive Userform
(A Microsoft Word Help & 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!

Click to donate British Pound Sterling                   Click to donate US dollars                   Click to donate EU euros

This Microsoft Word Tips & Microsoft Word Help page supplements Create and Employ a Userform. Here I will show you several methods for displaying userform data in your document and for making information in your document interactive with the controls (i.e., text fields, list boxes, etc.) on the userform.

In everyday use there will be instances where users may wish 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 linked and interactive with the userform then a blank userform will be displayed and the user will have to manually re-enter the information.

The basics:

First we will look at some basic userform text field data.  The following illustration shows a basic Userform with several text fields.

interactive userform 1

When the userform is presented, the user enters initial data and populates the document with the data entered.

interactive userform 2

Data from a userform can be displayed in the document in several ways. The following illustration provides examples:

interactive userform 3

The follow code is used to populate data from the example userform into the document segment shown above. The code is contained in a standard project module.

VBA Script:
Copy to clipboard 
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
  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
  'Call (full code is available in the template)
  FullDocumentUpdateFields
lbl_Exit:
  Exit Sub
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:

VBA Script:
Copy to clipboard 
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 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
lbl_Exit:
  Exit Sub
End Sub

Site Note Icon Note: 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 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.

VBA Script:
Copy to clipboard 
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
lbl_Exit:
  Exit Sub
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
lbl_Exit:
  Exit Function
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 change the table cell data in the document example and then redisplay the userform.

interactive userform 4

interactive userform 5

Site Note IconNote:  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.

Advanced application:

Now let's look at the more challenging aspects of making userform list boxes, combo boxes, checkboxes, and option buttons interactive with document data. Consider the following userform that has been filled out by a user:

interactive userform 6

Populated, the data in the document appears as shown below:

interactive userform 7

The follow code is used to populate the form data into the document:

VBA Script:
Copy to clipboard 
Sub PopulateDocumentII(ByRef oUF As UserForm)
Dim oDoc As Word.Document
Dim strEdu 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 strEdu = strEdu + "_High School"
    If .chkBach Then strEdu = strEdu + "_Bachelors"
    If .chkMasters Then strEdu = strEdu + "_Masters"
    If .chkPhD Then strEdu = strEdu + "_PhD"
    If .chkRS Then strEdu = strEdu + "_Reform School"
    'Clean up and format the string 
    strEdu = Replace(strEdu, "_", ", ")
    If Len(strEdu) > 2 Then strEdu = Right(strEdu, Len(strEdu) - 2)
    On Error Resume Next
    strEdu = Left(strEdu, InStrRev(strEdu, ",") - 1) & " and" & Mid(strEdu, InStrRev(strEdu, ",") + 1) 
    On Error GoTo 0
    'Set the education bookmark data
    SetBMRangeValues "bkmEducation", strEdu
    '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
      'Set the CC color selection data
    Select Case True
      Case .optCCRed
        oDoc.SelectContentControlsByTitle("Color Selection").Item(1).DropdownListEntries(2).Select
      Case .optCCBlue
        oDoc.SelectContentControlsByTitle("Color Selection").Item(1).DropdownListEntries(3).Select
      Case .optCCGreen
        oDoc.SelectContentControlsByTitle("Color Selection").Item(1).DropdownListEntries(4).Select
      Case Else
        oDoc.SelectContentControlsByTitle("Color Selection").Item(1).DropdownListEntries(1).Select
  End Select
  End With
lbl_Exit:
  Exit Sub
End Sub

As in the first example, code in the UserForm_Initialize event is used to display the form an make it interactive with existing document data.

VBA Script:
Copy to clipboard 
Private Sub UserForm_Initialize()
Dim i As Long
Dim strData 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
    strData = GetBMRangeValue("ListboxValueBM")
    For i = 0 To .listboxDemo1.ListCount - 1
      If .listboxDemo1.List(i) = strData 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
    strData = GetBMRangeValue("bkmEducation")
    If InStr(strData, "High School") > 0 Then .chkHS.Value = 1
    If InStr(strData, "Bachelors") > 0 Then .chkBach.Value = 1
    If InStr(strData, "Masters") > 0 Then .chkMasters.Value = 1
    If InStr(strData, "PhD") > 0 Then .chkPhD.Value = 1
    If InStr(strData, "Reform School") > 0 Then .chkRS.Value = 1
    'Set the option button values from data in the document
    Select Case GetBMRangeValue("bkmColor")
      Case "Red"
        .optRed.Value = 1
      Case "Blue"
        .optBlue.Value = 1
      Case "Green"
        .optGreen.Value = 1
    End Select
    Select Case ActiveDocument.SelectContentControlsByTitle("Color Selection").Item(1).Range.Text
      Case "Red"
        .optCCRed.Value = 1
      Case "Blue"
        .optCCBlue.Value = 1
      Case "Green"
        .optCCGreen.Value = 1
    End Select
  End With
lbl_Exit:
  Exit Sub
End Sub

Site Note IconBonus Tips
    1. For more information on populating userform lists and combo boxes, see my: Populate Userform Listbox
    2. In the example here, plain text is used in the document to reflect user checkbox choices.  Checkboxes can be interactive too!! See:  Interactive Userform Checkboxes

You can download a document contains the examples and code used to produce this Microsoft Word Help & Tips page here:  Interactive Userform

That's it! I hope you have found this tips page useful and informative.

Share

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!

Click to donate British Pound Sterling                   Click to donate US dollars                   Click to donate EU euros

Search my site or the web using Google Search Engine

Google Search Logo