The VBA MS Office Developers Journal

The Client request: “Do you know much about inserting page numbers into Word docs? I have had a few requests for it; if it is easy enough I might do it. I tried the Page number but it messes up my formatting. Since you know some cool tricks, I thought I would see if you have one for this that will not mess up my documents. If there is not, I will skip it, as there is not a ton of people asking!”.

I should start be saying I wrote code that takes a database of values and slices and dices in numerouse ways. 

The resulting documents Templates that are quite dense with edge to edge data that often results in the need tweak them to keep it all the data on the page.  To complicate matters there are fixed columns and copyright  info that must be displayed all of which need to be squeezed in.

My solution was simple, I call it a floating page number. Use these steps to duplcate it.
1. Insert a textbox into your template
2. Make the text in that template a “1” or page 1 whatever you like.
3. Insert a new module and add the following code into your word template document. Make sure your Template document is the activedocument.
4. run it from the VBE, make sure the immediate window is visible and make note of the textbox name in the immediate window.
5. Caefully replace “Text Box 1000” with the name from step 4 and your textbox will be renamed “MyPageNo”    

1 Sub GetTBName()
2 ' use selection.ShapeRange.Name to get the name
3 Dim shp As Shape, i as integer
5 With ActiveDocument
6 For i = 1 To .Shapes.Count
7 Debug.Print .Shapes(i).Name, .Shapes(i).TextFrame.TextRange.Text
8 If .Shapes(i).Name = "Text Box 1000" Then
9 .Shapes(i).Name = "MyPageNo"
10 End If
11 Next i
12 End With
14 End Sub

Code Snippets by Ray Mills
This is my solution - Always thoroughly test and retest your code!!

1 Sub PgNoUpdate(sPG As String)
2 Dim shp As Shape, i as integer
4 With ActiveDocument
5 For i = 1 To .Shapes.Count
6 ' Debug.Print .Shapes(i).Name
7 If .Shapes(i).Name = "MyPageNo" Then
8 .Shapes(i).TextFrame.TextRange.Text = sPG
9 End If
10 Next i
11 End With
13 End Sub

Code Snippets by Ray Mills
This is my solution - Always thoroughly test and retest your code!!

Take advantage of the layout window and choose the layout options that work best for your unique situation.  For my situation with super tight spacing the displayed selection seemed to work best.



In my situation I was running the code in Excel controling Word.  For this demonstration I modified it to run in Word.  The PgNoUpdate subroutine is passed a page number and it changes the text “1” in the active docuement Textbox “MyPageNo” to the passed sPG string. 

If you were able to utilize what you learned here or improve the code,
please leave a comment Here


If you enjoyed this post or found it helpful andl would like to say hello
and buy me a cup of coffee Click here

Raymond Mills, M.B.A., M.S.  has spent over 20 years of his career as Accountant, Investment Bank and Credit Card Technical Auditor/ Data Analyst.  His specialty was using Excel to get Big Databases including Teradata, Oracle,  Squel Server and Sybase to give up their secrets. Ray has said “I love nothing better than using VBA to unleash the power of Microsoft Office.” You can contact Ray @ 484 574-3190 or by emailing him Here

If you have a challenge with Excel, Access or Word and would like to speak with Ray,   You can get his contact details by clicking here: Contact Me