To show page number in a cell
 

Hello,

Is it possible to show the page number in a cell, instead of using "Header
and Footer"?

Thanks!

To show page number in a cell
 

One way, using a UDF:

(If you're not familiar with UDFs:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

should get you started)

Public Function PageNumber( _
Optional ByRef rng As Excel.Range) As Variant
Dim pbHorizontal As HPageBreak
Dim pbVertical As VPageBreak
Dim nHorizontalPageBreaks As Long
Dim nVerticalPageBreaks As Long
Dim nPageNumber As Long

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

Call from a cell using

=PageNumber()

for the page number of the cell the formula is in, or

=PageNumber(J20)

to find the page number of cell J20


NOTE that the function's result will not match printed page numbers if
multiple sheets are selected.


In article ,
Florence wrote:

Hello,

Is it possible to show the page number in a cell, instead of using "Header
and Footer"?

Thanks!

To show page number in a cell
 

It's too great!! Thanks a lot!!

Florence

"JE McGimpsey" wrote:

One way, using a UDF:

(If you're not familiar with UDFs:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

should get you started)

Public Function PageNumber( _
Optional ByRef rng As Excel.Range) As Variant
Dim pbHorizontal As HPageBreak
Dim pbVertical As VPageBreak
Dim nHorizontalPageBreaks As Long
Dim nVerticalPageBreaks As Long
Dim nPageNumber As Long

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

Call from a cell using

=PageNumber()

for the page number of the cell the formula is in, or

=PageNumber(J20)

to find the page number of cell J20


NOTE that the function's result will not match printed page numbers if
multiple sheets are selected.


In article ,
Florence wrote:

Hello,

Is it possible to show the page number in a cell, instead of using "Header
and Footer"?

Thanks!