Displaying a Telephone Number in VBA

A number of readers of VBA for Dummies have now asked me how to insert a telephone number into documents using a macro. It turns out that this is a common topic online. Microsoft should have added this feature some time ago, but apparently hasn’t (and probably won’t at this point). Most of the solutions I’ve seen are complex when this really isn’t a complex task. This post discusses three techniques:

 

  • Insertion as text
  • Insertion in a field
  • Insertion in a control on the document.


Insertion as Text

The first approach is to insert the telephone number as text. You obtain unformatted input from some source, use the Format() function to format it, and then insert it at the current cursor location.

The first thing you need to do is gain access to the developer tools if you’re using Office 2010. The VBA in Office 2010 post tells you how to perform this task. Create a new module for your document or template using the instructions I’ve provided in the Adding a Module section in Chapter 3 of VBA for Dummies. At this point, you can create a new macro for inserting the telephone number. Here’s some simple code you can try.

Sub InsertTelephoneNumber1()
    ' Obtain the telephone number.
    Value = InputBox("Type the telephone number", "Telephone Number")
     
    ' Insert the telephone number directly.
    Selection.InsertAfter Format(Value, "(###) ###-####")
End Sub

The InputBox() call simply provides the means for obtaining a telephone number. You can use any source desired. The Selection.InsertAfter call inserts any text you provide after the current cursor location. The Format() function takes the input you received as the first argument (Value) and formats it using the picture you provide. You can find information about the Format function on MSDN at http://msdn.microsoft.com/library/office/gg251755.aspx.

Insertion as Field

The second approach is working with a field. It’s a little harder to create a field that contains a telephone number, but only slightly. Formula field codes are somewhat misunderstood and this particular example uses a trick that I haven’t seen others use online for some odd reason. This is one time when using the Macro Recorder will save you time. I discuss using the Macro Recorder in the Using the Macro Recorder section in Chapter 3 of VBA for Dummies. I used these steps in Office 2010 to create the example that follows later.

 

  1. Start the Macro Recorder. Call your macro InsertTelephoneNumber2 or anything else appropriate and add a comment.
  2. Choose Insert | Quick Parts | Field. You see the Field dialog box shown here.
    Telephone01
  3. Select the = (Formula) option in the Field Names list and then click Formula. You see the Formula dialog box shown here.
    Telephone02
  4. Type any ten digit number in the Formula field as shown.
  5. Type (###) ###’-‘#### in the Number Format field as shown. Notice especially the use the single quotes (‘) around the dash. This addition is where most people make their mistake in working with these formula fields. Any text you surround with a single quote like this is treated as text. Otherwise, the dash will simply disappear.
  6. Click OK. Word will add the field to the display. Notice that the field is formatted precisely as you’d expect, including the dash.
  7. Stop the Macro Recorder. Word will create the macro for you.


At this point, you have a macro that will insert a field containing a specific telephone number. To make the field more generic, you’ll need to modify the code to accept input. Here’s a simple version that you can try.

Sub InsertTelephoneNumber2()
    ' Obtain the telephone number.
    Value = InputBox("Type the telephone number", "Telephone Number")
     
    ' Use a field to display the telephone number.
    Selection.InsertFormula Formula:="=" + Value, NumberFormat:= _
        "(###) ###'-'####"
End Sub

Again, the code obtains a telephone number using an InputBox. You can use any source desired. It then calls Selection.InsertFormula with the content you want to use as the first argument and the format for that content as the second argument. Notice that that call relies on named arguments to avoid any potential problems. In addition, notice that the equals sign (=) is in double quotes (“), but that Value is outside of those double quotes because you want the value it contains, and not the word “Value” as input to InsertFormula.

Insertion as Control

The third technique is to create a control, allow the user to input the data, and then reformat the text in the control. Controls are different because they require a little better understanding of how Word works. When you have a control, it generates events. The event handler listens for those events and then does something about them. Think about a house fire. There is a fire in your home. So, you go out to the woods and yell, “There is a fire in my home!” Nothing happens. That’s because there is no handler in the woods for the event of a fire in your home. When you call 911, the 911 operator, the event handler, listens to you yell, “There is a fire in my home!” Now something happens. The 911 operator calls the fire trucks and sends them to your house to put out the fire.

It’s time to see how all of this works in the real world. I’m assuming that you’re using Word 2010 and that you have saved your document in .DOCM (document with macros) format. The following steps get you started.

 

  1. Choose Developer | Plain Text Content Control. You see a new Plain Text Content Control added to your document.
  2. Choose Developer | Properties. You see the Content Control Properties dialog box shown here.
    Telephone03
  3. Type Telephone Number in the Title field and TN in the Tag field. Click OK. Word changes the properties of the Plain Text Content Control.
  4. Choose Developer | Visual Basic. You see the Visual Basic editor open.
  5. Right click the Project (<Document Name>)\Microsoft Word Objects\ThisDocument object in the Project Explorer window and choose View Code from the context menu. You see a module window open. (If you don’t see the Project Explorer window, choose View | Project Explorer to display it.)
  6. Select Document from the Object drop down list box in the module window (the one on the left). Word may automatically create an event handler for the New event. Remove this code because you don’t need it.
  7. Select ContentControlOnExit from the Procedure drop down list box in the module window (the one on the right). Word automatically creates the Document_ContentControlOnExit() event handler for you.


Now that you have an event handler, you have to tell it to do something. Just as the 911 operator needs to know what to do, so does Word. Here is an example of the code you could use to automatically format user input as a telephone number.

Private Sub Document_ContentControlOnExit( _
    ByVal ContentControl As ContentControl, _
    Cancel As Boolean)
 
    ' Verify the control's name.
    If ContentControl.Tag = "TN" Then
     
        ' Format the data in the control.
        ContentControl.Range.Text = _
        Format(ContentControl.Range.Text, "(###) ###-####")
    End If
End Sub

The Document_ContentControlOnExit() event handler receives two variables when Word calls it. The first contains all the information you need about the control that generated the event. The second lets you cancel the action when you detect a problem.

You only want to change controls that have a Tag value of TN. So, the first task is to detect controls of this type. The code then uses the ContentControl.Range.Text property to change the control’s text using the Format() function.

Bottom Line

These are three simple methods for adding a formatted telephone number to a Word document. However, you can extend these methods to format any sort of content. Just remember some of the rules you’ve learned here about working with formatted fields and then spend a little time with the Microsoft documentation to learn what else you can do. Please let me know if you have any questions about these techniques at John@JohnMuellerBooks.com.

 

Author: John

John Mueller is a freelance author and technical editor. He has writing in his blood, having produced 99 books and over 600 articles to date. The topics range from networking to artificial intelligence and from database management to heads-down programming. Some of his current books include a Web security book, discussions of how to manage big data using data science, a Windows command -line reference, and a book that shows how to build your own custom PC. His technical editing skills have helped over more than 67 authors refine the content of their manuscripts. John has provided technical editing services to both Data Based Advisor and Coast Compute magazines. He has also contributed articles to magazines such as Software Quality Connection, DevSource, InformIT, SQL Server Professional, Visual C++ Developer, Hard Core Visual Basic, asp.netPRO, Software Test and Performance, and Visual Basic Developer. Be sure to read John’s blog at http://blog.johnmuellerbooks.com/. When John isn’t working at the computer, you can find him outside in the garden, cutting wood, or generally enjoying nature. John also likes making wine and knitting. When not occupied with anything else, he makes glycerin soap and candles, which comes in handy for gift baskets. You can reach John on the Internet at John@JohnMuellerBooks.com. John is also setting up a website at http://www.johnmuellerbooks.com/. Feel free to take a look and make suggestions on how he can improve it.