Incrementing Page Numbers?

[Tosty]

Good morning everyone! I haven't been able to find a formula to accomplish this, and maybe there isn't one, but I figured if anyone could figure it out, it would be the people at Mr. Excel, so here goes.

I have created a form in Excel 2003 that will be printed on standard 8-1/2"x11" paper on a regular laser or inkjet printer. What I want to do is to have designated cells on my form indicate form number ?? of ??, and then when I print the form have the pages increment (or decrement) by (1) each time a copy of the form prints. For example: If I need to print 50 of the forms, the designated cells would indicate...1 of 50, 2 of 50, 3 of 50, 4 of 50, etc.

I would be grateful if anyone has any ideas on how to accomplish this. I must forewarn you that I have no experience with Visual Basic, so if that is the only way to accomplish this, you'll have to explain it to me like I am a child.

Thank you for any help you can provide!

Respectfully,
Tosty

 

[Ifrite]

The simplest way in my view is to create a little counting macro on your form and combine it with the print function (by creating a button out of your print range). You can easily perform this task by using "record macro" function without manipulating any VBA codes in the editor.

 

[SuperFerret]

Hi Tosty,​

I've used this on a recent project at work, so you may be able to amend this to your needs:​

Dim NewPageNumber As Integer 
[LEFT]Dim NumberOfInvoices As Integer

Sub IncPgNo()
[LEFT]' Checks for a number of times to print in J2 
If Range("J2") = "" Then
MsgBox ("You Must Enter A Number In J2 For Number Of Copies")
End
End If
NewPageNumber = Range("J1").Value
Do Until NumberOfInvoices = Range("J2").Value
  With ActiveSheet.PageSetup
      .FirstPageNumber = NewPageNumber
  End With
ActiveSheet.PrintOut
NumberOfInvoices = NumberOfInvoices + 1
Range("J1").Value = Range("J1").Value + 1 'J1 being using in a CONCATENATE formula within print range to give page # of #
Loop[/LEFT]
 
[LEFT]End Sub[/LEFT]
[/LEFT]

To implement: In your workbook press ALT then F11, keep ALT pressed and hit I then M (this will insert a new Module) paste the above into the large white space.
Then you can create a button outside the print range and assign this to it, assign to QAT or run from the Developer tab in Macros ​

 

[Tosty]

Thank you for the tip Ifrite, it is much appreciated. Would it be possible for you to explain the procedure in a little more detail? I have only had very basic practice with recording a macro.

Respectfully,
Tosty

 

[Tosty]

Thank you for your response SuperFerret, I appreciate it very much. As I said I have no experience with visual basic, so is it possible for me to copy/paste your code into the VB editor? And if so, how do I access the editor to accomplish this? Thank you for your patience.

Respectfully,
Tosty

 

[SuperFerret]

As I said I have no experience with visual basic, so is it possible for me to copy/paste your code into the VB editor? And if so, how do I access the editor to accomplish this? Thank you for your patience

Quite alright, I was once there myself

Ok, with the workbook you want this to work open:
press ALT and F11 - You should get the Microsoft Visual Basic Editor open
Now press ALT I then ALT M - This will insert a new module
Paste the code into the large white space

Do you know how to run a Macro once it is in there?

Are you confident enough to edit the ranges in the code to fit your workbook? (currently J1 and J2)

 

[Tosty]

Hi SuperFerret,

Thanks again for your quick response. To be quite honest, I'm not really sure what you are referring to with regard to editing the ranges. I am trying to set this up so other users can just print the sheet and the numbers will increment automatically, so even if I can teach myself how to run the macro, I was hoping that it would just run automatically when someone else printed the form. As an example, I was hoping they would only have to input the total number of forms they need into a cell, and then when they printed it, it would automatically print out 1 of 50, 2 of 50, 3 of 50, etc. Maybe I'm asking too much of Excel (or perhaps my own skill level)?

Thank you so much for your assistance, and I hope I haven't wasted your time.

Respectfully,
Tosty

 

[Ifrite]

Tosty,

Please watch this video to get yourself familiar with how to record a macro first
http://www.youtube.com/watch?v=KKJj78LoFaA
You can achieve your goal without programming anything manually but I believe that you have to use a macro.
After watching the video, you are ready to follow the steps below.

a. Before recording, please type 0 at A1 & B1, and input formula =A1+B1 at C1 and then input this formula
="Page "&C1&" of 50" at any other cell where you wanna show the note;
b. Start to record a macro (absolute macro) and define a shortcut
c. Go to B1 and type 1
d. Print the sheet
e. Copy C1 value to A1 (not formula, Copy by using Paste Special - Value)
f. Delete B1 value
g. End recording
h. use your defined shortcut to perform the job and please make sure that A1, B1 and C1 are out of print range.

I hope this helps.

 

[SuperFerret]

Tosty,

There is a way to tailor this for you, so if you answer the following I can amend as necessary and give you the updated code.

1. What Cell contains your Page # of #?

2. Do you have 2 Cells outside the print area you can give me Cell Refs for?

3. Is it ok to have a button outside the print area to start the macro?

 

[Tosty]

SuperFerret, thank you for going to all the trouble of attempting to customize this for me. I don’t know if it will work or not, but the page # of # are each in their own separate group of “merged” cells. So I have it laid out like this: Page {CX78} of {DX78}.

As far as the cell refs go, if they must be outside the print area to function, cells A90 and B90 would work, but if it is okay for them to be in the print area you can use A85 and B85. I can hide whatever the cells contain with “white” text if necessary.

If possible, I would actually like to have the button within the print area as long as I can customize the size of the button and the caption on the button. If I can do that, it would be okay for the button to appear on the form when it is printed. If that is not possible, it would be acceptable to put it outside the print area. I would just have to instruct the people using the document on where to find the button to run the macro.

Since the number of documents to be printed will vary, the best possible scenario would be if the user just could enter the total number of documents needed, and then click the button once and it would print the required number of documents with the pages numbered correctly. That way if they had to print 120 pages, they wouldn’t have to press the button 120 times.

Thanks again for all your help. If this is just too much for Excel to do, I certainly understand.

Respectfully,
Tosty