AutoMacro to change cell formats on printing

Posted by Steve Lincoln on April 20, 2001 10:13 AM

I am trying to develop a macro that is activated when a print command is sent such that it will change conditional cell formatting. I can't figure out how to link a macro initiation to an "onprint" event.Help files like beforeprint event and workbookbeforeprint event are confusing and not useful as far as I can see.Please Help TIA.

Posted by Dax on April 20, 2001 10:21 AM

Hello,

I think you'll find that the WorkbookBeforePrint is the event you should use in order to fire a macro when a user attempts to print a worksheet. Try something like this:-

Open the VB editor, double click on the ThisWorkbook icon and enter some code like this:-

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Enter your conditional formatting code here.
End Sub

Hope this helps,
Dax.

Posted by Dave Hawley on April 20, 2001 10:22 AM

Hi Steve

What you need is your code placed with the Private module of "ThisWorkbook". The quickest way to get there is to right click on the sheet picture, top left next to "File" and select "View Code"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Your Code Here
End Sub

Dave

OzGrid Business Applications

Posted by Steve Lincoln on April 25, 2001 11:51 AM

Thanks for the help on placing the Beforeprint event in this workbook module.That works great. I have written code designed to reformat a range and then I want it to print and following printing I want to change the format back to its default. The trick I am looking for is how to accomplish this with one subroutine initially autoactivated by file\print so that printing is carried out before the format goes back to default. Thanks so much.

Steve Lincoln