I’ve been developing some “smart” forms a government department in the last few months. These forms had to be developed in good old MS Word. Now, Word is not really suited to creating tightly controlled forms, but, in my case it’s all I had to work with and I’ve had to work within the limitations presented to me. The only real solution available was to use Word Content Controls and then to protect portions of the document that users shouldn’t be able to edit. I’ve managed to get things working fairly well and thought it worth sharing a bit of code I’ve developed along the way that should help anyone working with Microsoft Word Content Controls and VBA.

Data Validation

If you’re doing forms then data validation is critical. It’s pretty straightforward if you’re working with content controls if you make use of the ContentControlOnExit event. For example, here’s a bit of basic validation that ensures the value entered into a field is currency:

Private Sub Document_ContentControlOnExit(ByVal CC As contentControl, Cancel As Boolean)
  Dim sngTotalCost As Single
  Dim oCC As contentControl
  Select Case CC.Tag
    Case "sTotalCost"
      If Not validateCurrency(CC.Range.Text) Then
        Cancel = True
        Beep
        CC.Range.Select
        Exit Sub
      Else
        CC.Range.Text = Format(parseCurrency(CC.Range.Text), "$#,###0.00")
      End If
  End Select
End Sub

Public Function validateCurrency(sValue As String) As Boolean
  Dim iLoop As Integer
  Dim bReturn As Boolean
  Dim iAsc As Integer
  
  On Error GoTo errorHandler
  
  bReturn = False
  
  validateCurrency = bReturn
  
  sValue = Trim(sValue)
  sValue = Replace(sValue, "$", "")
  sValue = Replace(sValue, ",", "")
  
  If Len(sValue) = 0 Then
    validateCurrency = True
    Exit Function
  End If
  
  For iLoop = 1 To Len(sValue)
    iAsc = Asc(Mid(sValue, iLoop))
    
    If iAsc = Asc(".") Or (iAsc >= Asc("0") And iAsc <= Asc("9")) Then
    
    Else
      Exit Function
    End If
  Next iLoop
  validateCurrency = True
  Exit Function
errorHandler:
  MsgBox "An error has occurred" & vbCrLf & "Module: ThisDocument" & vbCrLf & "Procedure: validateCurrency" & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description, vbOKOnly
  Err.Clear
End Function

Public Function parseCurrency(sValue As String) As Single
  Dim iLoop As Integer
  Dim iAsc As Integer
  On Error GoTo errorHandler
  parseCurrency = 0
  
  sValue = Trim(sValue)
  sValue = Replace(sValue, "$", "")
  sValue = Replace(sValue, ",", "")
  
  If Len(sValue) = 0 Then
    parseCurrency = 0
    Exit Function
  End If
  
  For iLoop = 1 To Len(sValue)
    iAsc = Asc(Mid(sValue, iLoop))
    
    If iAsc = Asc(".") Or (iAsc >= Asc("0") And iAsc <= Asc("9")) Then
    
    Else
      Exit Function
    End If
  Next iLoop
  parseCurrency = Round(CSng(sValue), 2)
  Exit Function
errorHandler:
  MsgBox "An error has occurred" & vbCrLf & "Module: ThisDocument" & vbCrLf & "Procedure: parseCurrency" & vbCrLf & "Error Number: " & Err.Number & vbCrLf & "Description: " & Err.Description, vbOKOnly
  Err.Clear
End Function

In this code we simply check the tag of each content control as users move to the next one. Content control tags are set on the Developer tab from within Word. If the control has a tag we’re interested in then the value (Range.Text) is run through the ValidateCurrency function. If it is valid then the parseCurrency function is used to format the value correctly and write it back to the content control contents. If the value entered isn’t valid then a user alert is raised and the focus is returned to the content control

Excel Style “Automatic” Calculations

If you’ve got data validation sorted out it’s a simple step to have read only fields in your forms whose values are derived from user entered fields. This bit of code takes the value of a currency field, multiplies it by 1.1 and writes the value into a second field.

Private Sub Document_ContentControlOnExit(ByVal CC As contentControl, Cancel As Boolean)
  Dim sngTotalCost As Single
  Dim oCC As contentControl
  Select Case CC.Tag
    Case "sTotalCost"
      If Not validateCurrency(CC.Range.Text) Then
        Cancel = True
        Beep
        CC.Range.Select
        Exit Sub
      Else
        CC.Range.Text = Format(parseCurrency(CC.Range.Text), "$#,###0.00")
      End If
      Set oCC = ActiveDocument.SelectContentControlsByTag("sTotalCost").Item(1)
      sngTotalCost = parseCurrency(oCC.Range.Text)
      
      Set oCC = ActiveDocument.SelectContentControlsByTag("sTotalCostGST").Item(1)
      With oCC
        .LockContents = False
        .Range.Text = Format(sngTotalCost * 1.1, "$#,###0.00")
        .LockContents = True
      End With
  End Select
  Set oCC = Nothing

End Sub 

It’s pretty simple to see what’s happening here. The value in the content control with the tag sTotalCost is validated for currency, and if a correct value the number has a calculation applied to it and the resultant value is written to a second content control with the tag sTotalCostGST. Note that I use the LockContents method to be able to write to the second content control and then use it again to make it read only.

Changing Document Format Based on Content Control Value

The last thing I’ll share is using the value in a content control to change some format in a Word document. In this case I simply set the font color of the content control based on the value selected in the control

Private Sub Document_ContentControlOnExit(ByVal CC As contentControl, Cancel As Boolean)
  Dim sngTotalCost As Single
  Dim oCC As contentControl
  Select Case CC.Tag
    Case "sTag1", "sTag2", "sTag3"
      If CC.Range.Text = "Yes" Then
        CC.Range.Font.ColorIndex = wdGreen
      End If
      If CC.Range.Text = "No" Then
        CC.Range.Font.ColorIndex = wdRed
      End If
  End Select
  Set oCC = Nothing
End Sub

These content controls were of the dropdown list type. When “Yes” is selected the font color is set to green with the ColorIndex property. When set to “No” the Font.ColorIndex is set to Red. Pretty simple.