McGimpsey & Associates : Excel : UDFs : Displaying the Page Number of a cell

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)

Where

	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
        Application.Volatile
        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&
                Else
                    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
    ResumeHere:
        Exit Function
    ErrHandler:
        'Could use much more error handling...!
        PageNumber = CVErr(xlErrRef)
        Resume ResumeHere
    End Function

This page last updated Saturday, 31 March 2007

McGimpsey & Associates

Home

General XL Tips

XL Formulae

Using VBA with XL

XL VBA Functions

LongDec2Bin()

MultiCat()

PageSetupXL4M()

PrevSheet()

RandInt()

SampleNoReplace()

SumBold()

UnixToXLTime()

XL VBA Macros

Mac XL

Printing the Page Number of a cell

Quick Links