Automatically update a number when printed

[excelhelp]

Hi, I am trying to make a report with a report number at the top on excel. I
am trying to figure out if I can type in a report number ie. 1001 and then
everytime I print it, it updates so I dont have to change it to 1002, 1003
etc. Thanks for any suggestions

 

[Jacob Skaria]

Best way is to use the Workbook_BeforePrint event to restrict the user to
print from the menu/menu icons....

From workbook press Alt+F11 to launch VBE (Visual Basic Editor). From the
left treeview search for the workbook name and click on + to expand it.
Within that you should see the following

VBAProject(Your_Filename)
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)
Sheet3(Sheet3)
This Workbook

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

and then have a command button in the sheet to print a certain portion of
the worksheet and update the number....

If you dont want to disable the menu printing.. then use the
Workbook_BeforePrint event to call another procedure to update the
number...But the problems with this way is that even if the user goes to
'Print Preview mode' and cancel the procedure will still be called; or if the
user goes to Print preview mode before printing the procedure will be called
twice.....It works fine if the user directly print without print preview....

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Sheet2" Then Application.OnTime Now, "UpdateNumber"
End Sub


--From menu 'Insert' a module and paste the below code.

Private Sub UpdateNumber()
Range("B1") = Range("B1").Value + 1
End Sub