Bookmark page
Printable version
Course updatesForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel page numbers into variables
|
|
|
Bookmark this page|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel page numbers into variables
Resolved · Medium Priority · Version 2003
Reply
Excel page numbers into variables
I want to get the page number that a given cell in the current print selection will appear on into a variable or a cell outside the print area - how can this be done?For upcoming training course dates see: Pricing & availability
RE: Excel page numbers into variables
Hi RoyThanks for the question, its a tricky one.
There is no easy property or method of a cell to tell you which page it is on. There are ways of calculating it but they are complex.
To find out if a cell is in the print area you can use
not intersect(range("d1"),Range("print_area")) is nothing
which will return True if D1 is inside the print area. This only works if a print area has been setup.
Does that help atall?
Laura GB
RE: Excel page numbers into variables
Hi Laura,Thanks for the reply - useful to know but doesn't achieve what I'm trying to do, which is to make sure an index page in a multi page report matches the page numbers Excel will use when it prints it (the greyed 'watermark' that shows when using the page break preview.)
So as Excel knows what page numbers it will use, I'm trying to get it to tell me.
How complex is the way of calculating it?
Roy
RE: Excel page numbers into variables
Hi RoyI have found a vba function that will calculate the page numbers. It has a flaw in that it will not recalculate correctly unless you go and edit the cell, but it does work quite well otherwise.
Function PageNumber() As Integer
Dim VPC As Integer
Dim HPC As Integer
Dim VPB As VPageBreak
Dim HPB As HPageBreak
Dim NumPage As Integer
'Decide which way to count thge pages
If ActiveSheet.PageSetup.Order = xlDownThenOver Then
HPC = ActiveSheet.HPageBreaks.Count + 1
VPC = 1
Else
VPC = ActiveSheet.VPageBreaks.Count + 1
HPC = 1
End If
NumPage = 1
'Count the pages up and down
For Each VPB In ActiveSheet.VPageBreaks
If VPB.Location.Column > ActiveCell.Column Then Exit For
NumPage = NumPage + HPC
Next VPB
'Count the pages across
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
PageNumber = NumPage
End Function
I hope that helps.
Laura GB
|
 |
|
» Forum post: Filtering |
Hi
