Validate UserForm Text Entry

Home Up Odds & Ends Photo Gallery Search Contact Me Privacy Notice

 

 

 The information in this website is provided without risk or obligation and free of charge.  However, if you have benefitted from my efforts here and would like to make a contribution to help me continue and maintain this work then any donation will be greatly appreciated. Please click the adjacent button to access PayPal.  Thank you.
 

This Microsoft Word Help & Tips page will guide you how to create a basic Userform and validate the data entered in one of the text fields.  You will also learn how to set bookmark ranges in the document that display the data entered on the UserForm .  Here is a sample representation Word document displaying a few elements of data gathered in a UserForm (i.e., an employee's name and SSN):

Note:  The dimmed grey brackets are the bookmark boundaries.

An index to User Form Frequently Asked Questions (FAQs) can be found here:   Microsoft Word MVP FAQ (then click the User Form tab).  Use How to create a Userform to learn the basic steps to create a UserForm. 

Note:  The example give in the "How to create a Userform" does work.   Some people have problems getting the code to work because these lines of code can seem broken:

  .Bookmarks("Text1").Range _
  .InsertBefore TextBox1
  .Bookmarks("Text2").Range _
  .InsertBefore TextBox2 

They aren't broken but the error that some people experience in these split lines is due to how the code is pasted in the VB Editor.  To correct the problem, you can revise the code as follows:

  .Bookmarks("Text1").Range.InsertBefore TextBox1
  .Bookmarks("Text2").Range.InsertBefore TextBox2

Or see:  What are underscores at the end of lines code there for?

For this exericise, we will be using a form named "myUserForm" that consists of two labels, two textboxes and a command button.  Use the "Properties" window (if not showing press F4) in the VB Editor to define the UserForm name, UserForm caption, Label captions, and CommandButton caption as shown below. 

To change the properties, simply click on the control, then enter the property field on the properties window and change the default values to the values you want to use.

After you have the UserForm laid out, call it using an AutoNew macro stored in the document template.  This means that each time a new document (e.g., a new employee data sheet) is created using the template the Userform will automatically appear prompting you, the user, to enter data.  Here is the code to call the UserForm:

Sub AutoNew()
Dim myForm As myUserForm
Set myForm = New myUserForm
myForm.Show
Unload myForm
Set myForm = Nothing
End Sub
As we prepare each employee data sheet, we want to validate that the social security number contains exactly nine digits and only numeric characters.  We can do this using the "_Exit" event of the Textbox:

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox2
    If Not IsNumeric(.Text) Or Len(.Text) <> 9 Then
        MsgBox "Invalid characters or wrong number of numerical digits in field. " _
        & "Enter exactly nine numbers with no dashes."
        Cancel = True
        .SetFocus
        .SelStart = 0
        .SelLength = Len(.Text)
        End If
    End With
End Sub
As you can see in the code above, if the proper text conditions aren't met, then the exit event (tabbing or clicking out of the field) is canceled, a message displayed, and the form focus is reset to the invalid entry.
 

 

Only after a valid social security number is entered can the user continue to process the form.

The document contains two bookmarks as shown below:

When the user presses the "OK" command button, the validated data is formatted and transferred from the Userform to the document using the following code:
Private Sub CommandButton1_Click()
Dim oRng As Word.Range
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
Set oRng = oBM("bkName").Range
oRng.Text = Me.TextBox1.Text
oBM.Add "bkName", oRng
Set oRng = oBM("bkSSN").Range
oRng.Text = Format(Me.TextBox2.Text, "###-##-####")
oBM.Add "bkSSN", oRng
Unload Me
End Sub

Similiarly, you have a bookmark "PhoneNum" and validate a phone number entry like this:

Private Sub CommandButton1_Click()
Dim oRng As Word.Range
Dim oBM As Bookmarks
Set oBM = ActiveDocument.Bookmarks
Set oRng =oBM("PhoneNum").Range
oRng.Text = Format(Me.TextBox2.Text, "(###) ###-####")
ActiveDocument.Bookmarks.Add "PhoneNum", oRng
Unload Me
End Sub

Private Sub TextBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With Me.TextBox1
If Not IsNumeric(.Text) Or Len(.Text) <> 10 Then
MsgBox "Invalid characters or wrong number of numerical digits in field. " _
& "Enter exactly ten numbers with no dashes."
Cancel = True
.SetFocus
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Data entered in the Userform text fields can be easily repeated throughout the document by using REF fields.  Using our example above, if the employee name needed to be repeated you could use a construction like this:

If you use REF fields in your document, you will need to update the fields when the form is processed.  If the fields are located in the main text of the document, then simply adding:

ActiveDocument.Fields.Update

to the CommandButton1_Click routine will do the job.  Otherwise, see:  Field Macros

For more on repeating data in Word Documents, see:  Repeating Data

Looking for something else?

Google