Auto increment A1 cell after each printing

[Harry Geda]

Hello,

I would like to auto increment the cell A1 after every print.
Also if possible to have the luxury of entering a value in B1 for print quantity.
A1 should increment before printing the next print.
I am using the A1 as a serial number generator inside the form.

I am new to this. Only copied one macro before this.

Thank you,
Harry

 

[VoG]

I think that this will show the dialog that you want.

I don't know a way of preventing that code from running when printpreview is used so this gives you a choice:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim i As Long
If MsgBox("Run A1 increment code?", vbYesNo + vbQuestion) = vbNo Then Exit Sub
Cancel = True
Application.EnableEvents = False
Application.Dialogs(xlDialogPrint).Show
With Sheets("Sheet1")
    For i = 1 To .Range("B1").Value
        .Range("A1").Value = .Range("A1").Value + 1
        .PrintOut
    Next i
End With
Application.EnableEvents = True
End Sub

 

[Harry Geda]

Hello VoG,

Thank you for the nice addition of the "Print increment mode" dialog.

I placed A1=0 and B1=3 in sheet 1.

It printed 4 pages of the code.
A1=3 and B1=3

Also, how do I increment before printing?
My prints:
A1 B1
0 3 first page
1 3 2nd page
2 3 3rd page
4 3 4th page

Thank you,
Hrayr

 

[VoG]

That sounds like you are clicking the print button on the print dialog. Don't.

 

[Harry Geda]

VoG,

I do not understand your point.
This time I used Ctrl-P.

Started with:
1 3

The prints are:
A1 B1
1 3 first page
2 3 2nd page
3 3 3rd page
4 3 4th page

Regards,
Harry

 

[VoG]

I may be missing something here - isn't that exactly what you wanted to happen?

Sorry but if you keep changing your requirements then I am not willing to help further.

 

[Harry Geda]

VoG,

Sorry about the confusion.

The auto-increment works.
It seems that I am printing an extra page.
B1=3 and I print 4 pages.

Regards,
Harry

 

[Harry Geda]

I am not printing the 4 intentionaly. The program does that automaticaly.
4 pages instead of expected 3 that I write in cell B1.

 

[Harry Geda]

Can anyone else tell me how to fix the quantity problem?
My mentor is tired of answering me.

I place 3 in my B1 cell and the program prints 4 copies.
I suppose I can reduce the count by one or change something in the Visual Basic command to do the quantity properly but that will be a patch.

Is there any way to announce the question answerd?
VoG has put up with me alot and I do not know how to give credit to him.

Regards,
Harry

 

[AnubhavK]

VoG,

Thank you for beeing patient with me.

Now it works.
You allowed me to select printer but the original menu is still bypassed.
The standard printer dialog also allows you to do preview and then print.

Also, is it possible to not increment on preview?

Many thanks,
Harry

The second one was great. But I need it a bit more customized as I need the macro to print till the end of the serial number in the sheet. Please help...

 

[oldLogic]

Can anyone else tell me how to fix the quantity problem?
My mentor is tired of answering me.

I place 3 in my B1 cell and the program prints 4 copies.
I suppose I can reduce the count by one or change something in the Visual Basic command to do the quantity properly but that will be a patch.

Is there any way to announce the question answerd?
VoG has put up with me alot and I do not know how to give credit to him.

Regards,
Harry

It should print 3 pages

Try editing this line

For i = 1 To .Range("B1").Value

to

For i = 1 To .Range("B1").Value - 1

same as keying in 2 instead of 3 in B1