Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to display current page number in any cell of that page. | Excel Discussion (Misc queries) | |||
How do I get the page number to show up like a watermark in excel | Excel Discussion (Misc queries) | |||
Picking a cell to show in another page. | Excel Discussion (Misc queries) | |||
sheet tabs as page number and in a cell page of pages? | Excel Discussion (Misc queries) | |||
Cell to Show "Continued" only if > Page 1 | Excel Worksheet Functions |