Excel zone

Macro to update form number when it's printed

We have got a template form to fill the details for the incident. This form is created in Excel. We would like to give a form number in cell b5, this number should increased by one each form after printed one copy. So each form has unique number. Currently we increase the number manully and print one copy, then again we change the number and print. I just wondered, if macro do the job for us. I appreciate any help on this subject. Thank you in anticipation.

Nilesh
Nilesh Mandvia


Number of comments: 6

Print Friendly Mail to Friend Post Comment

---

Nilesh Mandvia, 1-May-2007

Thank you
Alastair and Alan, thank you very much for your help on this problem.

Kind regards

---

Alastair Harris, 29-Mar-2007

keeping my promise
change the before print code in the "this workbook" module to:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If Worksheets("sheet1").Range("printflag") = False Then
Cancel = True
End If
End Sub

This assumes a range called printflag in sheet 1; which you will have to create. It does not have to be in sheet 1, but if it is in another sheet then you will have to change the reference

then the print handling code - I have this in the sheet 1 module related to the new printme button (or commandbutton1):

Private Sub CommandButton1_Click()
Worksheets("sheet1").Range("printflag") = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("b5") = Range("b5") + 1
Worksheets("sheet1").Range("printflag") = False
End Sub

If you click the button it changes the printflag value to true; runs the excel print command (which works becase of the test in the before print procedure above); increases the unique number, and resets the printflag to false.

---

Alastair Harris, 29-Mar-2007

something more robust
is possible.

I would suggest you disable the excel printing - this is simply by an amendment to the before print code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Then you need a separate procedure to handle the printing, which you attach to a button in the spreadsheet. Essentially this needs to set a flag to enable printing (and you will need to test that flag in the before print code and set cancel to true or false accordingly) and then increment the unique number. If you want to get real flash you could add the ability for the user to select how many forms to print (separate non printing cell or a user form).

This is relatively simple VBA once you know how. If no one beats me to it I will post examples later.

---

Nilesh Mandvia, 28-Mar-2007

vb
We use normal spread sheet, office 2003, saving the updated same spreadsheet. We use this spreadsheet purley for form purposes.

I used vb print before functions, but I hit another problem, it will increase one number and print 5 copies with the same number. I would like to print say 10 copies at a time but with form number increased by one at copy printed.

Thank you very much for your help.

Kind regards

---

Alan Webb, 28-Mar-2007

Be aware that
Worksheet_BeforePrint also runs if you print preview a worksheet, so whilst you may get unique numbers there may be gaps in the sequence and if you print a form twice it will have different numbers.

How do you use the template? As a true Excel Template - so from File : New - or just the same worksheet each time saving the updated form?

---

Alastair Harris, 28-Mar-2007

vba
The following code goes in the "thisworkbook" object

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Range("b5") = Range("b5") + 1
End Sub

It adds one to the cell before it prints the form (rather than after).

Hope this helps.