The following code updates an invoice number in a master invoice file, after
a separate invoice file (e.g. one invoice per customer) has been printed.
Having a master invoice number in a separate file enables multiple users to
print different invoices, while maintaining a chronological invoice number.
The value of this master invoice number can be linked to your Excel
worksheet.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
On Error GoTo ErrorHandler

'cancel printing so we can print in this procedure, then do our own
after print
Cancel = True
'disable events so beforeprint isn't called again
Application.EnableEvents = False

With Sheet1
.PrintOut


' Open the source file
' for local drive
'Workbooks.Open ("C:\Documents and Settings\BaronVS\My
Documents\Weekly new Invoices\master invoice number.xls")
'for a network share
Workbooks.Open ("\\OFFICE1\SharedDocs\master invoice number.xls")


' change value (invoice number) _after_ printing
' instead of changing a cell we change the master invoice number
ActiveWorkbook.Sheets(1).Range("A1").Value =
ActiveWorkbook.Sheets(1).Range("A1").Value + 1

'Close the master invoice file
ActiveWorkbook.Close SaveChanges:=True

' To display the invoice number in your worksheet, link a cell to
the value in the 'master invoice number' file

End With

ErrorHandler:
' want to make sure we re-enable events if there's an error
Application.EnableEvents = True
End Sub


W. Pooh (AKA Winnie P.)
"Don't eat soup! Soup makes you poop!"
--Baron von Stinkipantz III