In this Microsoft Word Help & Tips page I will show you how to create and employ a UserForm from a document template.  I have held off publishing this page for over a year because there is already a page on this topic published on the MVP FAQ page  Create a UserForm by friend and fellow MVP Doug Robbins.  This page is not intended to diminish the helpfulness of that page.  However Doug's article provides only the bare bone basics where with this page I will attempt to enhance some of the methods and provide examples of several available UserForm controls.

For this article I am going to create a template and UserForm for a simple Student Survey form. The template contains the boilerplate text in the Student Survey, DocVariable fields placed at "most" locations in the template where variable data gathered by the UserForm will be placed, a bookmark for one specific data element (i.e., the address data), a VBA code module for creating document variables and processing the UserForm, and the UserForm object with its associated processing code.  An illustration of the template opened in Word (with field codes displayed) is shown below:

On Doug's suggestion, with one exception I am departing from an often used practice of using bookmarks as placemarker\data stores in the document and will use document variables and DocVariables fields instead.  Document variables and DocVariable fields are easier to code and you have the advantage of using the \*CharFormat in the DocVariable fields.  This lets you add different formatting to the text at each DocVariable field (note the DocVariable field located in the  template header).  The data in that field will be formatted to match the "D" in "DocVariable.  The exception is that I will use a bookmark for the "Address" information.  At one time I thought I "needed" to use a bookmark to process the multi-line input. That is not the case.  However, I still want to illustrate a method for putting data "in" a bookmark instead of "at" a bookmark as was shown in the MVP FAQ article.  Having  data in a bookmark allows you to reference that data and repeat it using REF fields at other locations in the document.  For more on Repeating data and populating fields see my:  Repeating Data (or populating fields)

If you don't know what a template is or if you need help creating one see the MVP FAQ:  Create a Template.  For this exercise I named my template Student Survey Form.dotm.

If you are using Word2007 you must use a .dotm (or macro enabled) template.

DocVariable fields can be entered using Insert Tab>QuickParts>Field>DocVariable ribbon sequence (Word2007) or Insert>Field>DocVariable menu sequence (Word97-2003); or you can simply type the field text e.g., DocVariable varName, select it, and then press CTRL+F9.  Bookmarks are entered using Insert Tab>Link>Bookmark ribbon sequence (Word2007) or Insert>Bookmarks.. menu sequence (Word97-2003)

I used one uncommon but very handy DateAndTimePicker control for entering the birthday. 

Unfortunately Microsoft no longer includes the Microsoft Windows Common Control-2-6-0 (SP3) in the resource library.  You need this reference for the DateAndTimePicker control to work.  If you can't find it in the list of available resources then you will need to download MSCOMCT2.OCX (see Office Help & Support KB 297381).  Once you have saved MSCOMCT2.OCX in your system directory (e.g., C:\Windows\System32  for Windows XP Professional) you will need to register it.  Click "Start" then "Run," type in regsvr32 c:\windows\system32\mscomct2.ocx, then click OK.  If you don't want to go the trouble of doing this, I have included a second UserForm in the .dot version of the Student Survey Form template that substitutes a Textbox control for birthday and omits the DateAndTimePicker control.
 

You may have noticed that the ListBox and multi-select ListBox in the example form has items displayed and one of the Gender option buttons is selected.  This is accomplished using the UserForm "Initialize" event.  This event establishes the conditions in the UserForm when it is initially displayed to the user.  The code for the Student Survey Form is: 

Students being students, you might want to validate some of the UserForm inputs.  While the Spinbuttons are used to increment the value in the age field, we want to ensure that only a numeric value less than 120 is entered.  We are also going to use the Age field input to set the year displayed in the DateAndTimePicker control to a value near the the students birth year.

    Private Sub txtAge_Change()
    Dim pSpan
    If Not IsNumeric(Me.txtAge) Then
        If Len(Me.txtAge) > 1 Then
           Me.txtAge.Text = Left(Me.txtAge.Text, Len(Me.txtAge.Text) - 1)
        Else
            Me.txtAge.Text = ""
        End If
        Exit Sub
    End If
    If Len(Me.txtAge) > 3 Then
       Me.txtAge.Text = Left(Me.txtAge.Text, 3)
    End If
    If Val(Me.txtAge) > 120 Then
       MsgBox "Contact Guiness World Records!!"
       Me.txtAge.Text = Left(Me.txtAge.Text, 2)
    End If
    pSpan = Format(DateAdd("yyyy", -(CDbl(Me.txtAge)), Now), "yyyy")
    Me.DTPicker1 = CDate("1/1/" & pSpan)
End Sub

You might want to ensure that fields are not left blank or comment on a particular entry.  You can do this with the "OK" click event as follows:

    Private Sub cmdBtnOK_Click()
    Select Case ""
       Case Me.txtName.Value
          MsgBox "Please fill-in your name."
          Me.txtName.SetFocus
          Exit Sub
       Case Me.txtAge.Value
          MsgBox "Please fill-in your age."
          Me.txtAge.SetFocus
          Exit Sub
       Case Me.txtAddress.Value
          MsgBox "Please fill-in your address."
          Me.txtAddress.SetFocus
          Exit Sub
       Case Me.txtPhone.Value
          MsgBox "Please fill-in your phone number."
          Me.txtPhone.SetFocus
          Exit Sub
    End Select
    If Me.obUndecided.Value = True Then
       MsgBox "You should schedule an appointment with the school counselor", _
                       vbExclamation + vbOKOnly, "Don't know your gender?"
       Exit Sub
    End If
    'Set value of a public variable declared at the forms level."  This is used to determine if _
    form was processed or if the student canceled.
    Me.boolProceed = True
    Me.Hide
    End Sub

Option Explicit

Sub CallUF()
Dim oFrm As frmSurvey
Dim oVars As Word.Variables
Dim pStr As String
Dim oRng As Word.Range
Dim i As Long
Dim pMulSel As String
Set oVars = ActiveDocument.Variables
Set oFrm = New frmSurvey
With oFrm
   .Show
   If .boolProceed Then
      oVars("varName").Value = .txtName
      oVars("varAge").Value = .txtAge
       'Replace the line breaks entered by the user with line breaks and tabs _
      to ensure address entry is properly indented.  See notes below.
      pStr = Replace(.txtAddress.Value, Chr(10), Chr(10) + Chr(9))
      Set oRng = ActiveDocument.Bookmarks("bmAddress").Range
      oRng.Text = pStr
      ActiveDocument.Bookmarks.Add "bmAddress", oRng
      oVars("varBirthDay").Value = .DTPicker1
      'Define the Gender text
      Select Case True
         Case .obMale
             pStr = "Male"
         Case .obFemale
             pStr = "Female"
         Case .obUndecided
             pStr = "Undecided"
      End Select
      oVars("varGender").Value = pStr
      oVars("varPhone").Value = .txtPhone
      'Process student responses (including no response)
      If Not IsNull(.LBFavSub.Value) And (.LBFavSub.Value) <> "" Then
         oVars("varFavSub").Value = .LBFavSub.Value
      Else
        oVars("varFavSub").Value = "Not provided."
      End If
      If .CBFavTeach.Value <> "" Then
         oVars("varFavTeach").Value = .CBFavTeach.Value
      Else: oVars("varFavTeach").Value = "No response"
      End If
      If .CBFavFood.Value <> "" Then
         oVars("varFavFood").Value = .CBFavFood.Value
      Else: oVars("varFavFood").Value = "No response"
      End If
      'Define the PersItems text string
      If .CheckBox1.Value = True Then pStr = "Cell phone, "
      If .CheckBox2.Value = True Then pStr = pStr & "Car, "
      If .CheckBox3.Value = True Then pStr = pStr & "MP3 Player, "
      If .CheckBox4.Value = True Then pStr = pStr & "Bullwhip."
      'Clean up the string text
      If Right(pStr, 2) = ", " Then pStr = Left(pStr, Len(pStr) - 2) & "."
          On Error Resume Next
          pStr = Left(pStr, InStrRev(pStr, ",") - 1) & " and" & Mid(pStr, InStrRev(pStr, ",") + 1)
          On Error GoTo 0
          If pStr = "" Then pStr = "No response"
          oVars("varPersItems").Value = pStr
          'Build the multi-select string
          pMulSel = ""
          With .LBmultisel
             For i = 0 To .ListCount - 1
                If .Selected(i) Then
                   pMulSel = pMulSel & .List(i) & ", "
                End If
             Next i
          End With
          'Clean up the string text.
          If Right(pMulSel, 2) = ", " Then pMulSel = Left(pMulSel, Len(pMulSel) - 2) & "."
          On Error Resume Next
          pMulSel = Left(pMulSel, InStrRev(pMulSel, ",") - 1) & " and" & Mid(pMulSel, InStrRev(pMulSel, ",") + 1)
          On Error GoTo 0
          If pMulSel = "" Then pMulSel = "No Response"
          oVars("varFavSports").Value = pMulSel
          UpdateThisFormsFields
   Else
      MsgBox "Form cancelled by user"
    End If
End With
Unload oFrm
Set oFrm = Nothing
Set oVars = Nothing
Set oRng = Nothing
End Sub

Notes:

1.  Notice that it takes two additional steps and lines of code to process the bookmark for the address data.  Whenever you place data "in" a bookmark instead of "at" a bookmark you must redefine the bookmark range.  This destroys the bookmark and another one with the same name must be added at the redefined range.  This may seem like a lot of trouble but it is worth the extra effort.

2.  If you elect to use a DocVariable for a multi=line textbox and want to indent the subsequent lines to tab stop you would use the following code:

oVar("varAddress").Value = Replace(Me.TextBox1.Text, Chr(10), Chr(9))

In the procedure above we have set the value of several document variables to the value that the user enter entered in the UserForm. We entered the DocVariable fields when we created the template.  Those fields need to be updated to the new variable valuse.  We accomplished this by calling a separate procedure  "myUpdateFields" that cycles through each storyrange in the document and updates the fields.  This procedure is shown below:  

Sub myUpdateFields()
Dim pRange As Word.Range
Dim iLink As Long
iLink = ActiveDocument.Sections(1).Headers(1).Range.StoryType
For Each pRange In ActiveDocument.StoryRanges
   Do
      pRange.Fields.Update
      Set pRange = pRange.NextStoryRange
   Loop Until pRange Is Nothing
Next
End Sub

Field Codes displayed

Field result displayed

Sub Create_Reset_Variables()
With ActiveDocument.Variables
    .Item("varName").Value = " "
     .Item("varAge").Value = " "
     .Item("varAddress").Value = " "
     .Item("varBirthday").Value = " "
     .Item("varGender").Value = " "
     .Item("varPhone").Value = " "
     .Item("varFavSub").Value = " "
     .Item("varFavTeach").Value = " "
     .Item("varFavFood").Value = " "
     .Item("varFavSports").Value = " "
     .Item("varPersItems").Value = " "
End With
UpdateThisFormsFields
End Sub

Sub AutoNew()
Create_Reset_Variables
CallUF
End Sub

This concludes this Tips Page.  I hope that it gives you a better understanding and appreciation for UserForms.  There are several other helpful articles on UserForms in the Word MVP FAQ pages  MVP FAQ UserForms

Looking for something else?

	Enter your search terms Submit search form
	Web gregmaxey.mvps.org