General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions


XL VBA Macros

Mac XL

This site will look better if you upgrade to a browser that supports web standards.

Printing the Page Number of a cell

There's no built-in function that will display the page number a particular cell will print on. The UDF below will provide that function.

Call this as

    = PageNumber(Cell, Start)


	Cell        The cell of interest. If omitted,
			    the cell that contains the formula
	Start       The starting page number. If omitted, 1

Note: This will not return the printed value if multiple worksheets are selected.


   'Purpose: Display the page number a cell will be printed on
   'Inputs:  target The cell of interest
   '         nStart The starting page number
   'Returns: The page number of the target cell. Returns
   '         #Ref! if there's an error

      Public Function PageNumber( _
Optional ByRef target As Excel.Range, _
Optional ByVal nStart As Long = 1&) As Variant
Dim pbHorizontal As HPageBreak
Dim pbVertical As VPageBreak
Dim nHorizontalPageBreaks As Long
Dim nPageNumber As Long
Dim nVerticalPageBreaks As Long
Dim nRow As Long
Dim nCol As Long

On Error GoTo ErrHandler
If target Is Nothing Then _
Set target = Application.Caller
With target
nRow = .Row
nCol = .Column
With .Parent
If .PageSetup.Order = xlDownThenOver Then
nHorizontalPageBreaks = .HPageBreaks.Count + 1&
nVerticalPageBreaks = 1&
nHorizontalPageBreaks = 1&
nVerticalPageBreaks = .VPageBreaks.Count + 1&
End If
nPageNumber = nStart
For Each pbHorizontal In .HPageBreaks
If pbHorizontal.Location.Row > nRow Then Exit For
nPageNumber = nPageNumber + nVerticalPageBreaks
Next pbHorizontal
For Each pbVertical In .VPageBreaks
If pbVertical.Location.Column > nCol Then Exit For
nPageNumber = nPageNumber + nHorizontalPageBreaks
Next pbVertical
End With
End With
PageNumber = nPageNumber
Exit Function
'Could use much more error handling...!
PageNumber = CVErr(xlErrRef)
Resume ResumeHere
End Function

Valid XHTML 1.1 Valid CSS Made on a Macintosh

Quick Links

Don't know how to use User Defined Functions? See David McRitchie's Getting Started with Macros and User Defined Functions