Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Automatically update a number when printed
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I automatically update column index number in VLookup whe. | Excel Worksheet Functions | |||
Update one worksheet tab to automatically update all other tabs? | Excel Worksheet Functions | |||
number of pages will be printed | Excel Discussion (Misc queries) | |||
Excel - need an serial # to update everytime sheet is printed. | Excel Worksheet Functions | |||
automatically update a number in cell when template opens | Excel Discussion (Misc queries) |