Prev: SUMPRODUCT help with dates
Next: Is it possible to hide column headings & row numbers?
From: excelhelp on 30 Apr 2010 05:31
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
From: Jacob Skaria on 30 Apr 2010 05:58
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
 | 
Pages: 1
Prev: SUMPRODUCT help with dates
Next: Is it possible to hide column headings & row numbers?