“Page x of y” in cell

Topic

New Reply

I am trying to enter a pagination function that will return “Page X Of Y” in a cell on several worksheets, where X is the current page number and Y is the total number of worksheets. Any ideas?

Reply | Quote

Replies

Hi,
I don’t know of any built in way of doing it but you could use a user-defined function like this:
Function PageNum() As String
Application.Volatile
PageNum = “Page ” & Application.Caller.Parent.Index & ” of ” & Sheets.Count
End Function
to do it (assuming that by page number you mean sheet number rather than a reference to the number in the printing order)
Hope that helps.

Reply | Quote

Rory,
That’s neat – I’d not seen Application.Caller before. It reminded me of a question, though. If I put that function in my Personal.xls to use in any workbook, I’d have to enter
“=Personal.xls!PageNum()” rather than just “=PageNum()”.
In order for the latter to work, would I need to use an add-in?

Reply | Quote

Hi Colin,
You could either do it as an add-in or paste the code into each workbook (a pain, but it does mean that it will work when others are looking at it). I’m still looking for a code-free way of doing it as it’s not that strange a thing to want to do!

Reply | Quote

If you have two workbooks, the value returned by that function gives the total number of pages of the active book, regardless of the sheet the function is actually in.

If Workbook A has 3 sheets and sheet 1 contains that formula, but Workbook B, with 10 sheets, is active the formula in Book A returns Page 1 of 10.

Just something I noticed as it may have significance in certain circumstances.

Regards,

Andrew C

Reply | Quote

Oops! Thanks, Andrew.
Revised formula (hopefully correct this time):
Function PageNum() As String
With Application
.Volatile
With .Caller.Parent
PageNum = “Page ” & .Index & ” of ” & .Parent.Sheets.Count
End With
End With
End Function

Reply | Quote

Do you and jlkirk mean sheet X of Y instead of Page?

When using the code, if you use the function in cell A1 on sheet2, you get 2 of X, the 2= sheet 2, not the first page that would be printed on sheet 2. Or am I just confused by what is meant by “where X is the current page”

Also, Sheets.count is going to return the total number of sheets including chart sheets. Worksheets.count will return the number of worksheets not including any chart sheets. (Didn’t know if that’s a consideration)

j/w

Reply | Quote

Hi Tom,
I did mention in my original post that I had assumed that Page meant Sheet rather than anything to do with print pages. I should probably have mentioned the Sheets.Count bit though!

Reply | Quote

Rory,

Thanks for your response. I should have been clearer-the term “Page” actually refers to worksheets in the workbook. For example, “Page 2 of 10” would appear on the 2nd worksheet in a workbook containing 10 worksheets.

In addition, I am very unfamiliar with working with user-defined functions. How and where do I insert it in the subject workbook-which worksheet, what do I type, etc.?

Thanks,

Jeff

Reply | Quote

Hi Jeff,

To include Rory’s function you need to launch the VB Editor by pressing Alt-F11 whilst Excel is running. On the left window you should see a list of “Projects”. If you would like the function to be available to all your workbooks you should select VBAProject(Personal.xls), Click on Insert|Module. You can then copy the text of Rory’s function from the above and paste it into the large window of the VB editor.

The function should then be available and can be accessed through the paste function button (fx), where it will be shown as Personal.xls!PageNum. (If you want to enter it manually just type Personal.xls!PageNum().)

To retain the function don’t forget to save changes to Personal.xls before closing Excel.

Strictly speaking the function returns the Tab order of the sheet rather than the order in which the sheet was inserted. You could have Sheet1 with the above function returning Page 1 of 1 but if you insert a sheet before sheet1, it will return Page 2 of 1, even though the sheet name is still Sheet1. This is probably a good thing, but something that you might like to be aware of.

Hope I did not confuse you,

Andrew C

Reply | Quote

Andrew,
I guess I am dumb, but when I do as you instructed, there is not listed a “VBAProject(Personal.xls)”.
Also, what exactly do I paste?
Finally, I’m not sure I understand what you are trying to tell me in the 3rd paragraph of your reply.
Thanks,
Jeff

Reply | Quote

Jeff,

You are not dumb, I am. I should not have assumed that you had a personal.xls.

Just to get the thing working and to avoid further confusion, select whatever workbook you have open, a good one being the one you want to have the function operational in. So instead of VBAProject(Personal.xls) you could find VBAProject(OpenBook.xls) wher OpenBook is the name of the book you have open.

Then copy the following green text ( I hope Rory does not mind) :

Function PageNum() As String
With Application
.Volatile
With .Caller.Parent
PageNum = “Page ” & .Index & ” of ” & .Parent.Sheets.Count
End With
End With
End Function

and paste as described before. If that does not work let me know.

The 3rd paragraph of last reply merely says that if you change the tab order of the sheet the “page” number changes accordingly. (the sheet tab is that little appendage at the bottom that shows the sheets name)

Hope I have not confused you even more.

Reply | Quote

Andrew,
I tried it (at least I think I did) but it returned that it function had no arguments, or something like that.
If I could, would you mind setting this thing up in the attached workbook to number the 6 Sample worksheets? If possible, design it so the “Page X of Y” appears in A1 of each worksheet. Also, once I have it on this on, how can I make it available for all of my excel workbooks?
Whew! I never thought such a simple task could be so involved!
Thanks,
Jeff

Reply | Quote

Hi Jeff,
Attached is your sample workbook with the function in it. To make it available to all workbooks you’ll either have to paste the code into each one or paste it into Personal.xls and call it from there (if you do that you’ll need to use =Personal.xls!PageNum() as your formula)
Just to satisfy my curiosity, why do you want to do this? I can understand it on a printout but have never seen it used for on-screen documents.

Reply | Quote

Rory,
Thanks.
How do I “activate” it?
Also, my original intent was to have it just show up on my printout!
Thanks,
Jeff

Reply | Quote

Hiya,

for the non-vba minded, here’s the best I can come up with. Cedit where due to David Hager.
(eee03 for those who are interested. Anybody know what happened to those, by the way?)

a) define the name shtPOS as =GET.DOCUMENT(87)

enter the following formula in all sheets in A2

=”Sheet ” & shtPOS & ” of ” & COUNTA(Sheet1:Sheet3!A1)

c) put a title in all sheets in A1

i) I can’t think of a way to do it without names.
ii) It would be a lot neater if anyone knew how to return the total sheet through GET.DOCUMENT() I couldn’t locate this in any of the eee’s i’ve got(1 thru 19).

HTH

Brooke

Reply | Quote

Hi Jeff,
What do you mean by ‘activate’ it? The result should be appearing in cell A1 of each sheet unless you have macros disabled.
However, if you only need this for printing you can insert page numbers into a footer by choosing File-Page Setup, select the Header/Footer tab and in the footer (or header) dropdown box about the second item should be ‘Page 1 of ?’.
This will return the number of printed pages (i.e. each worksheet could be more than one printed page) rather than the number of sheets. Is that what you wanted? (if so, that will teach me to ask more questions before launching into code writing!! )

Reply | Quote

Rory,
Here is the spreadsheet. Whenever I print it, the cell where the “Page X of Y” should be returns “#NAME?”.
Any ideas?
Thanks in advance,
Jeff

Reply | Quote

Hi Jeff,
It works OK on my PC. If you look under Tools-Macro-Security… is it set on high? If so, this will disable macros (which includes user defined functions) and so you will get the #NAME error.
Does that help?

Reply | Quote

That seemed to cure the problem. Now, how can I make this available to any workbook I open up and use?
Thanks again!
Jeff

Reply | Quote

Jeff,
If you already have a personal macro workbook you can copy the code into a module in there and then change your formula to =Personal.xls!pagenum()
If you don’t already have a personal macro workbook, select Tools-Macro-Record New Macro, choose to store it in Personal Macro Workbook, select OK, then press Stop on the macro toolbar. This will create a workbook called Personal.xls in your XLSTART directory which will open (hidden) every time you start Excel. You can then copy the code into this workbook.
Hope that helps.

Reply | Quote

Thanks, Rory, I really appreciate your time and patience.
Jeff

Reply | Quote

My pleasure!

Reply | Quote

Rory,
Me again-any way to add this as an “add-in” so that it will automatically “attach” to a new file?
If not, please explain in detail (and in very simple terms for me) how I can add this feature to each Excel file I already have created or will create in the future.
Thanks in advance.
Jeff

Reply | Quote

Hi Jeff,
Sorry for the delay – I’ve been swamped recently. I see Andrew has kindly shown you how to add it to your personal macro workbook so this is more for information than anything:
1. You could make this available to future workbooks by adding the code to a new blank workbook and then saving that workbook as book.xlt in your xlstart directory.
2. You could make it available to all your workbooks by adding the code to a new blank workbook, saving the workbook as PageNum.xla in your add-ins directory and then go to Tools-Add-Ins and check PageNum. You can then simply type =PageNum() in whichever cell you want. It’s probably a bit over the top to create an add-in for just one function though!

Reply | Quote

Rory,
Please see my post of yesterday.
Thanks,
jeff

Reply | Quote

Hi Jeff,

Have you set up a personal.xls as suggested. If you store the function in personal.xls, and provided that file is in your XLStart directory, the function should be available to all your workbooks.

Andrew C

Reply | Quote

Andrew,
Can you please walk me through the process once more? Sorry to be such a problem!
Thanks,
Jeff

Reply | Quote

Jeff no problem.

First check to see if you a personal.xls by pressing Alt-F11. The VBA editor should open. in the left pane you see a list with titles like VBAProject(Bookname) , where bookname represents the names of your open workbooks. If you can see VBAProject(PERSONAL.XLS) then there is not need to create a new one. If you do not see that project listed, then open a new blank workbook. Save it as Personal.xls in your XLStart directory. The path to XLStart depends on your installation of Office. Try searching for it with Windows Explorer.

You then need to copy the Function to personal.xls. To do this you might need to highlight VBAProject(Persoan.xls) and go to Insert Module. Then just enter the text of the function. When that is done go back to excel and select personal.xls., goto Window, Hide.

Close all other books and exit Excel. When asked to keep changes to personal.xls select yes. Exit Excel and restart. The function you created should now be available to all your workbooks. There is a snag in that =PERSONAL.XLS! needs to be inserted before the function name. However that can be easy by using Insert Function, and selecting User Defined.

Hope you can make sense of that.

Andrew

Reply | Quote

I forgot to mention that if you have the code in personal.xls other users will get an error if they’re looking at your worksheets – not sure if that will affect you but it’s worth knowing.

Reply | Quote