Page 1 of 2 12 LastLast
Results 1 to 15 of 30
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    "Page x of y" in cell

    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?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: "Page x of y" in cell

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: "Page x of y" in cell

    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?

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: "Page x of y" in cell

    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! [img]/w3timages/icons/smile.gif[/img]
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re:

    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

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re:

    Oops! [img]/w3timages/icons/blush.gif[/img] 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
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    188
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re:

    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

  8. #8
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: "Page x of y" in cell

    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

  9. #9
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Page (Sheet? or Tab Order?) x of y

    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

  10. #10
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Page (Sheet? or Tab Order?) x of y

    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

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re:

    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!
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Page (Sheet? or Tab Order?) x of y

    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) :<font color=448800>

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

    </font color=448800>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.

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Posts
    1,418
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Page (Sheet? or Tab Order?) x of y

    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
    Attached Files Attached Files

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Page (Sheet? or Tab Order?) x of y

    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.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Page (Sheet? or Tab Order?) x of y

    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.
    Regards,
    Rory

    Microsoft MVP - Excel

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •