Resting Anchor

The Anchorage

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

Validate Content Control Entries
(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!


There is an annoying, persistent "Bug" in the Word2007 ContentControlOnExit event that is manifest when the Developer tab is not the active ribbon tab and you attempt to navigate between content controls (CCs).

This Microsoft Word Help & Microsoft Word Tips page illustrates the bug and shows you how to work around it. When you've killed the bug then you can effectively use the Document_ContentControlOnExit to validate Content Control entries

Meet the Bug ...

To illustrate this "Bug" you can create a new document, add some introductory text and then add several CCs. For example let's add three CCs and title them CC1, CC2 and CC3 respectively.

validate cc entry 1

Open the VB Editor (ALT+F11).

VBA Script:
Copy to clipboard 
Option Explicit
Private Sub Document_ContentControlOnExit(ByVal ContentControl _
                                          As ContentControl, _
                                          Cancel As Boolean)
MsgBox ContentControl.Title & " " & " exit event fired"
End Sub

Your document VB project should appear as shown below.

validate cc entry 2

Click CC1 and then TAB from CC1 to CC2. You will see the appropriate message box display as expected.

Now save, close and reopen the document. Click CC1. What's going on?!! You simply entered CC1 but the Exit event appeared to fire. There's the "Bug."

When you reopened the document the "Home" tab is active (or more importantly, the "Developer" tab is inactive). The bug is associated with the state of the "Developer" tab (i.e., active or inactive).

Close and reopen the document again. This time before you click CC1, click the "Developer" tab. Now things are working again. Things will continue to work as long as the Developer tab is the active tab.  This requirement makes serious validation of content controls entries using the OnExit event practically impossible!!  This has been an issue since the application was released nearly 8 years ago.  Microsoft has failed miserably in resolving this issue and support Word 2007 users.  Fortunately it has been resolved in Word 2010.

If you don't have Word 2007, the solution and work around for this bug is to import two standard code modules "Main" and "Accessibility" in each project that contains content controls and a call in the Document_ContentControlOnEntry and Document_ ContentControlOnExit event procedures as shown below:

Site Note IconThe "Main" and "Accessibility" modules contain code developed by Word MVP Tony Jollans. Thanks Tony Animated gif - super!! They are available in the demonstration and examples document that you can download using the link at the bottom of this tips page. Using the Project Explorer pane you can right click and export each module to a folder on your PC. You can then import these modules from your storage folder into your projects as required.

validate cc entry 3

You can delete the former code and copy and paste this code in your project:

VBA Script:
Copy to clipboard 
Option Exlicit

Private Sub Document_ContentControlOnEnter(ByVal ContentControl _
                                           As ContentControl)
  If Application.Version < "14.0" Then Main.SetDeveloperTabActive
End Sub
Private Sub Document_ContentControlOnExit(ByVal ContentControl _
                                          As ContentControl, _
                                          Cancel As Boolean)
  If Application.Version < "14.0" Then Main.SetDeveloperTabActive
  MsgBox ContentControl.Title & " " & " exit event fired"
End Sub

If you have imported the "Main" and "Accessibility" modules into your document you can now save, close and reopen the document again. This time when you click CC1, the "Developer" tab is activated by the OnEntry event and the OnExit event does not erratically fire.

Validation

Now that we have gained control of the OnExit event there are lots of neat things that you can do with your CCs.

For example, you can use the OnExit event is to perform data validation. You may need mandatory information (i.e., a CC that can't be left blank), or a phone number/SSN in a proper format, or some other rigid data format.

Let's add five more CCs to our document and title them "Name," "Whole Number," "SSN," "Pass Code," and "Phone Number." With the exception of "Name," which we will make a mandatory field, the other titles illustrate that a specific data format is required.

validate cc entry 4

VBA Script:
Copy to clipboard 
Option Explicit
Private Sub Document_ContentControlOnEnter(ByVal CC As ContentControl)
  If Application.Version < "14.0" Then Main.SetDeveloperTabActive
lbl_Exit:
  Exit Sub
End Sub
Private Sub Document_ContentControlOnExit(ByVal CC As ContentControl, _
           Cancel As Boolean)
Dim strTemp As String
Dim bGate1 As Boolean, bGate2 As Boolean, bGate3 As Boolean
Dim bGate4 As Boolean, bGate5 As Boolean, bValid As Boolean
Dim i As Long
Dim oRng As Word.Range
  strTemp = CC.Range.Text
  bValid = False
  If Application.Version < "14.0" Then Main.SetDeveloperTabActive
  Select Case CC.Title
    Case "Name"
      If CC.ShowingPlaceholderText = True Or strTemp = "" Then
        MsgBox "This field cannot be left blank."
        'Makes CC mandatory.  User can' exit until data is entered. 
        Cancel = True
      End If
    Case "Whole Number"
      If CC.ShowingPlaceholderText = True Or Not IsNumeric(strTemp) Then
        MsgBox "" & strTemp & "" & " is not a valid number. Try again."
        Cancel = True
        CC.Range.Text = ""
      ElseIf Val(strTemp) > 50 Or Val(strTemp) < 1 Then
        MsgBox "" & strTemp & "" & " is not between 1 and 50. Try again."
        Cancel = True
        CC.Range.Text = ""
      ElseIf Val(strTemp) <> Int(Val(strTemp)) Then
        MsgBox "" & strTemp & "" & " is not a whole number. Try again."
        Cancel = True
        CC.Range.Text = ""
      End If
    Case Is = "SSN"
      If Not strTemp Like "#########" And Not strTemp Like "###-##-####" Then
          Cancel = True
          MsgBox "Please enter the SSN in ""###-##-####"" format."
          CC.Range.Text = ""
        ElseIf strTemp Like "#########" Then
          strTemp = Left(strTemp, 3) & "-" & Mid(strTemp, 4, 2) & "-" & Right(strTemp, 4)
          CC.Range.Text = strTemp
        End If
    Case Is = "Pass Code"
      bGate1 = False
      bGate2 = False
      bGate3 = False
      bGate4 = False
      bGate5 = False
      If Len(strTemp) > 5 And Len(strTemp) < 10 Then
        bGate1 = True
        For i = 1 To Len(strTemp)
          If Mid(strTemp, i, 1) Like "#" Then bGate2 = True
          If Mid(strTemp, i, 1) Like "[A-Z]" Then bGate3 = True
          If Mid(strTemp, i, 1) Like "[a-z]" Then bGate4 = True
          If Mid(strTemp, i, 1) Like "[@$%&]" Then bGate5 = True
        Next i
      End If
      bValid = bGate1 And bGate2 And bGate3 And bGate4 And bGate5
      If Not bValid Then
        Cancel = True
        MsgBox "You did not enter a valid passcode. Try Again."
        CC.Range.Text = ""
      End If
    Case Is = "Phone Number"
      If (strTemp Like "(###) ###-####") Then Exit Sub
      If (strTemp Like "##########") Then
        strTemp = "(" & Left(strTemp, 3) & ") " & Mid(strTemp, 4, 3) & "-" & Right(strTemp, 4)
        CC.Range.Text = strTemp
      ElseIf (strTemp Like "###-###-####") Then
        strTemp = "(" & Left(strTemp, 3) & ") " & Right(strTemp, 8)
        CC.Range.Text = strTemp
        Exit Sub
      ElseIf (strTemp Like "### ### ####") Then
        strTemp = Replace(strTemp, " ", "-")
        strTemp = "(" & Left(strTemp, 3) & ") " & Right(strTemp, 8)
        CC.Range.Text = strTemp
      ElseIf (strTemp Like "(###)###-####") Then
        strTemp = Left(strTemp, 5) & " " & Right(strTemp, 8)
        CC.Range.Text = strTemp
      Else
        Cancel = True
        MsgBox "Invalid entry. Try again."
        CC.Range.Text = ""
      End If
   Case Else
     MsgBox CC.Title & " Exit event fired."
  End Select
lbl_Exit:
  Exit Sub
End Sub

That's it! With a functional OnExit event your ability to validate CC data is practically limitless.  You can create your own validation module as shown above or download and use a copy of the  document I used to create this tips page.  CC Validation Files.  I hope you have found this Microsoft Word Tips page informative and helpful.  For more topics on contents controls see my: Content Controls.

PAYMENTS/DONATIONS

Click to acces PayPal Verification Service Click to acces PayPal Verification Service

Do you want to make a payment for consulting work or donate to help support this site?

PayPal is a safe, easy way to pay online.

Use the appropriate currency "Donate" button to make a payment or donation.


Search my site or the web using Google Search Engine

Google Search Logo

Or

JustAnswerAsk a Word Expert OnlineSubmit