ADD 1 TO CELL UPON PRINT

[Sandra]

I have a cell in a worksheet that I want to add 1 to each
time the page is printed.
For example 4300 this time, 4301 next time, 4302 next.
I know this is easy in programming, but I don't know
where to start in Excel.

 

[Bernie Deitrick]

Sandra,

You could use the Before_Print event:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("A1").Value = Range("A1").Value + 1
End Sub

Change the A1 to the cell of interest.

Or you could use a macro to print multiple copies, incrementing the cell
each time through the loop.

HTH,
Bernie
MS Excel MVP

 

[papou]

Hello
Paste and amend the sample code below in the workbook (right click on the
Excel Icon, situated on left of the File menu, and choose View code)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Cancel <> True Then _
Worksheets("Feuil1").Range("A1").Value =
Worksheets("Feuil1").Range("A1").Value + 1
End Sub

HTH
Cordially
Pascal

 

[Harald Staff]

Hi

Open the VB editor (Alt F11 or similar). Open the ThisWorkbook module by
doubleclicking it in the project explorer up left. Paste this code in and
change the cell address in the code to fit your needs:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Sheets(1).Range("C3").Value = Sheets(1).Range("C3").Value + 1
End Sub

HTH. Best wishes Harald

 

[Tom Ogilvy]

go to the ThisWorkbook Module and in the dropdown at the top select workbook
from the left and BeforePrint from the right

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet2" Then
Cells(1, 1).Value = Cells(1, 1) + 1
End If
End Sub

This will increment the number just before printing. this isn't exactly
what you want, but this is what is supported - so you would start with zero
rather than 1.

 

[Sandra]

Bernie, this works great! What does "Cancel As Boolean"
mean?

Now, can you tell me how to have the program ask me how
many copies, accept my answer and print that many? I
think I know how to "call" the code below within the code
I just requested.

Thanks.

 

[Bernie Deitrick]

Sandra,

Instead of using the before print method, your could use a little macro:

Sub PrintCopies()
Dim i As Integer
Dim iCopies As Integer
iCopies = Application.InputBox("How many copies?")
For i = 1 To iCopies
Range("A1").Value = Range("A1").Value + 1
ActiveSheet.PrintOut
Next i
End Sub

HTH,
Bernie
MS Excel MVP

 

[Sandra]

Bernie, I copied and pasted the code. When I tried to run
the macro, I received this message: "Compile error:
Invalid Outside Procedure" ????

Thanks
Sandra

 

[Bernie Deitrick]

Sandra,

How are you running the macro, and what is the current selection when you do
it? Did you assign the macro to a button on the sheet, do you have a graph
selected, etc....

HTH,
Bernie
MS Excel MVP

 

[Sandra]

I tried from the sheet where the cell is to be changed.
I selected Tools > Macro > Macros > and chose PrintCopies.
I also tried from VBA while the macro was visible by
selecting Run > Run Sub/UserForm

Thank you.