VBA - Activate a macro upon printing

**RaineAKALotto** · 10-04-2012, 05:57 AM

Dear Excel Forum,

I am having an issue with an Excel/VBA-related task at work, something I have not been able to work out despite extensive research. I took some time to look through previous threads here on the forums, but since I have a deadline, I decided to ask away. I can work with graphs and functions, but not with VBA (I am a tech, not a programmer). I hope I have come to the right place for help.

I have managed to write a simple macro which, upon pressing a shortcut, adds 1 to a number in a specific field. I need to make this happen each time I print a copy of the worksheet. That is, if I choose to print 200 copies, they will be numbered 0000 - 0200, with a unique number printed on each copy.

This is the macro I managed to put together:

Please Login or Register  to view this content.

Could someone tell me how to make it work as I have described above? I would be really grateful.

**TMS** · 10-04-2012, 07:12 AM

Welcome to the forum.

There is an event for this, Workbook_BeforePrint:

Please Login or Register  to view this content.

Please note that, to comply with forum rules, you should add code tags to your code excerpt in the OP. Please take a few moments to read the forum rules.

Regards, TMS

**RaineAKALotto** · 10-04-2012, 07:48 AM

Thank you kindly for the rapid response!

I have one last question: where exactly do I insert this piece of code? In the module I have created, in a new module, perhaps someplace else? I have tried different things but so far, it did not work, yet I am certain this code is what I have been looking for.

Also, I apologize for brushing with the rules, I will keep them in mind the next time I make a thread.

**TMS** · 10-04-2012, 07:56 AM

It's a workbook event so it has to go in the Workbook Class Module. Double click on ThisWorkbook in the VBA Project pane. Paste the code in there.

No problem with the rules but it would be good (for both of us) if you edit the posts and add the tags.

Regards, TMS

**TMS** · 10-04-2012, 08:10 AM

Thanks for the rep and for updating the code tags.

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save

**RaineAKALotto** · 10-04-2012, 08:15 AM

Okay, another question - I finally have a basic understanding of this whole thing; the solution seems to have worked as the moment I click Print or press Ctrl+P, the number changes (just the way it's supposed to). However, this does not show on the actual printing; the number remains the same on each sheet. What could have gone wrong? Could it be the computer I'm using (it's running Vista)?

**RaineAKALotto** · 10-04-2012, 08:54 AM

Here's an update.

I have the following module

Please Login or Register  to view this content.

and the following piece of code in ThisWorkbook

Please Login or Register  to view this content.

What happens is, if I print out 2 sheets separately (that is, click "Print" twice), I do get the numbering right. What I'm looking to do is to automate the process, that is, be able to issue a single command to print a given number of copies with the aforementioned numbering (so basically an ever-growing number in a particular cell).

Any ideas how to do this?

Also, thanks TMShucks for the help, I did get a step closer to the solution. If you guys could help me out on this one, I'd be really grateful; I'll probably have to deal with Visual Basic in the future so I'm trying to learn, instead of simply relying on your help.