how to show page number in cell
Sam
number of pages in an cell of a worksheet?
I know how to do it with print preview, blah, blah...
is there anyway to do it within a worksheet?
Ron de Bruin
Sub Test()
Dim VPC As Integer, HPC As Integer
Dim VPB As VPageBreak, HPB As HPageBreak
Dim NumPage As Integer
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
For Each VPB In ActiveSheet.VPageBreaks
If VPB.Location.Column > ActiveCell.Column Then Exit For
NumPage = NumPage + HPC
Next VPB
For Each HPB In ActiveSheet.HPageBreaks
If HPB.Location.Row > ActiveCell.Row Then Exit For
NumPage = NumPage + VPC
Next HPB
ActiveCell.Value = "Page number of the active cell = " & NumPage
End Sub
Totpage = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)")
this will give you the page count
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"Sam" <sam.ru...@gasai.com> wrote in message news:061301c32a18$c3330bb0$a101...@phx.gbl...
David McRitchie
NumPages = ExecuteExcel4Macro("Get.document(50,"")")
Page number of the active cell, Find the page number of the activecell
(Sub not formula). Laurent Longre 2000-04-21 programming.
http://google.com/groups?oi=djq&ic=1&threadm=an_613974661
Some postings by Myrna Larson, that determine the page number
for the activecell and think there is one somewhere that does it for
a column of cells. (but could not find it) AN=626272822 2000/05/23,
AN=561640908 1999/12/16 .
http://google.com/groups?oi=djq&ic=1&threadm=an_626272822
http://google.com/groups?oi=djq&ic=1&threadm=an_561640908
You might also look at
Determining the Number of Printed Pages
http://www.j-walk.com/ss/excel/tips/tip65.htm
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm
"Sam" <sam.ru...@gasai.com> wrote in message news:061301c32a18$c3330bb0$a101...@phx.gbl...
Myrna Larson
I couldn't get my code that uses XLM function to work from a formula in a worksheet cell, so I
tried again with the pagebreaks collections.
I'm now using Excel XP. I thought I had the new code all working (it was "flaky" in XL2000),
when AGAIN the code would break inside a loop like this:
With ActiveSheet.HPageBreaks
N = .Count
For i = 1 to N
If .Item(i).Location.Row > R Then
It would crash with a "subscript out of range" error when i = 2 or 3 and N = 30 !!!
I also tried a faster approach where I loaded all of the row numbers and column numbers into
arrays. My code takes into account two possible "gotcha's": (1) the print area doesn't begin at
cell A1, so some pages at the top and/or to the left aren't printed, and (2) the formula is in a
cell that isn't in the print area. (Your function assumes neither of these 2 situations are
present.) To handle these situations, I had to access the pagebreaks collections several times,
and fetching all of the row numbers and column numbers once at the top speeded things up by a
factor of 2. But it was still slow -- took 0.6 seconds to identify a cell on page 60 on a 90
page sheet.
Both versions of the code crashed (same error), sometimes. With the 2nd version, it was in the
sub that pulled all of the row numbers from the collection into an array. Sometimes turning on
PageBreak Preview mode would get it working again, sometimes not. So they haven't fixed this
problem after all. Bummer!!!
Myrna Larson
Ron de Bruin
I hope in Office 11 or 12 it is a build in feature???
--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl
"Myrna Larson" <myrna...@charter.net> wrote in message news:pqsqdvcrla92pigaa...@4ax.com...
Myrna Larson
be more excited about the latter than the former.