Page numbering
in [Programming]
|
From: Lp12 on 16 May 2010 08:11 Hi, I want to have the "page n of nn pages" to be represented in a specific cell (not using the header functions). Is it possible to do so? Thanks a lot in advance
From: JLGWhiz on 16 May 2010 09:53 It is possible. This snippet will get you started. Sub dj() Sheets(1).DisplayPageBreaks = True pbRng = Sheets(1).HPageBreaks(1).Location.Address Range(pbRng).Offset(-50, 0) = "Pg 1 of 1" End Sub If the worksheet is using rows with standard height, then the snippet above would put the page number in cell A2. What the code has to do to number all pages is as follows: 1. count all HPageBreaks. (Top of page 1 does not count) 2. set up a loop that will: a. Use variable to find the next HPageBreak b. Use variables change the page of pages number Note that the first line in the snippet displays the page breaks. They must be visible for the count and location properties to work. "Lp12" <Lp12(a)discussions.microsoft.com> wrote in message news:BB85BF86-D71A-4133-AB91-3F130790A660(a)microsoft.com... > Hi, > I want to have the "page n of nn pages" to be represented in a specific > cell > (not using the header functions). Is it possible to do so? > Thanks a lot in advance
From: JLGWhiz on 16 May 2010 13:20 Not to discourage your efforts in creating a procedure to do what you want with the page numbering, but there are a few things to bear in mind as you do so. Excel is subject to users adding rows and columns as well as varying their height and width respectively. Cells might be shifted as users configure data on the worksheets and a few other quirks that can alter page numbers, even if you can get them calculated correctly the first time. Trying to write code to cover all of these possibilities could be a nightmare. I think that is why it is not a built in feature and why the internal calculation that is done is kept until the print preview is called. At that point, it can calculate what would be printed based on the page setup parameters that the user has entered. That is another glitch, the user can also change between protrait and landscape, or change the paper size. It goes on and on. While it might be convenient to see which printed page you are working on as you go through a worksheet, I am not sure it is a real advantage in terms of the effort it would take to write the code to get it. "Lp12" <Lp12(a)discussions.microsoft.com> wrote in message news:BB85BF86-D71A-4133-AB91-3F130790A660(a)microsoft.com... > Hi, > I want to have the "page n of nn pages" to be represented in a specific > cell > (not using the header functions). Is it possible to do so? > Thanks a lot in advance
From: Rick Rothstein on 16 May 2010 14:01 Another complication is the numbering can be selected by the user to be numbered either "down then over" or "over then down", so your code would have to take that sequential ordering method into account as well. On top of that, if I remember correctly, when there are non-contiguous areas selected (which may not be applicable to the OP's question), I believe the order the areas were selected in figures into how the page numbers are assigned. I looked at trying to develop a generalized routine to determine the page number for a given range and just met so many condition that needed to be accounted for that I wasn't able to complete the task; however, I did sit back after that experience and marveled over the fact that Excel can do all of what is required in the "blink of an eye"... I can just imagine what a "mess" the underlying Excel code has to be. -- Rick (MVP - Excel) "JLGWhiz" <JLGWhiz(a)cfl.rr.com> wrote in message news:#BcQPwR9KHA.4924(a)TK2MSFTNGP04.phx.gbl... > Not to discourage your efforts in creating a procedure to do what you want > with the page numbering, but there are a few things to bear in mind as you > do so. Excel is subject to users adding rows and columns as well as > varying their height and width respectively. Cells might be shifted as > users configure data on the worksheets and a few other quirks that can > alter page numbers, even if you can get them calculated correctly the > first time. Trying to write code to cover all of these possibilities could > be a nightmare. I think that is why it is not a built in feature and why > the internal calculation that is done is kept until the print preview is > called. At that point, it can calculate what would be printed based on the > page setup parameters that the user has entered. That is another glitch, > the user can also change between protrait and landscape, or change the > paper size. It goes on and on. While it might be convenient to see > which printed page you are working on as you go through a worksheet, I am > not sure it is a real advantage in terms of the effort it would take to > write the code to get it. > > > > > "Lp12" <Lp12(a)discussions.microsoft.com> wrote in message > news:BB85BF86-D71A-4133-AB91-3F130790A660(a)microsoft.com... >> Hi, >> I want to have the "page n of nn pages" to be represented in a specific >> cell >> (not using the header functions). Is it possible to do so? >> Thanks a lot in advance > >
From: Project Mangler on 16 May 2010 15:42
This is a case of where angels are too smart to tread: It only numbers down then over and puts n of nn pages into the top right cell in the page. I leave modifications as an exercise for the reader. Sub PageNr() Dim pNrs As String Dim cnt As Long Dim n As Long Dim p As Long Dim pgHLen As Long Dim pgVLen As Long Dim riteCol As Long Dim btmRow As Long Dim hBound As Long Dim vBound As Long btmRow = Cells(Rows.Count, 1).End(xlUp).Row riteCol = Cells(1, Columns.Count).End(xlToLeft).Column pgHLen = ActiveSheet.HPageBreaks.Item(1).Location.Row - 1 pgVLen = ActiveSheet.VPageBreaks.Item(1).Location.Column - 1 If btmRow Mod pgHLen > 0 Then hBound = 1 Else hBound = 0 End If If riteCol Mod pgVLen > 0 Then vBound = 1 Else vBound = 0 End If pNrs = (ActiveSheet.HPageBreaks.Count + hBound) * (ActiveSheet.VPageBreaks.Count + vBound) cnt = 1 For p = 1 To riteCol Step pgVLen For n = 1 To btmRow Step pgHLen ActiveSheet.Cells(n, p) = cnt & " of " & pNrs & " pages" cnt = cnt + 1 Next n Next p End Sub "Lp12" <Lp12(a)discussions.microsoft.com> wrote in message news:BB85BF86-D71A-4133-AB91-3F130790A660(a)microsoft.com... > Hi, > I want to have the "page n of nn pages" to be represented in a specific cell > (not using the header functions). Is it possible to do so? > Thanks a lot in advance |