Automatically update a number when printed
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
--
Jacob (MVP - Excel)
"excelhelp" wrote:
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
|