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 |
4 | |
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 |
13 | |
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 |
3 | |
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 |
12 | |
13 | End Sub |
Code Snippets by Ray Mills
This is my solution - Always thoroughly test and retest your code!!

Experiment!
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

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