I want to use the BeforePrint event in Excel in order to edit the header and footer of the final printout depending on various parameters. In order for the user to check whether the page setup is right, this code needs to be called before the print preview within Excel is shown. When calling the print preview with the VBA code ThisWorkbook.PrintPreview, the event fires before the preview is shown, but when using "Ctrl+P" or "File - print" the event is not called until the document is sent to the printer, so the print preview does not show the final printout. The code Application.CommandBars.ExecuteMso "PrintPreviewAndPrint" also leads to the same unsatisfying result.
I've noticed, that when using ThisWorkbook.PrintPreview the preview is shown in full screen mode while the other methods show the 'normal' print preview. To test if the event is called, I used the following short piece of code.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Debug.Print "BeforePrint"
End Sub
I need a foolproof way of showing the correct print preview, so using a button with the VBA code mentioned above is no option. Any suggestions?
Thanks in advance, mk
Even though you have not disclosed which Excel version you are using (it might be vital in this case). I am afraid that getting .BeforePrint event called before the Printing preview when using Ctrl+P might not be possible in your case since "Excel 2013 and later doesn't run Workbook_BeforePrint on Print Preview." (user OzRoz, 2017)
The above answer is taken from a thread which was asking similar question. In the answer, OzRoz also mentions that "you can get it to run for testing by adding the 'Print Preview Full Screen' icon to your QAT (Quick Access Toolbar)"
Perhaps this provides a definitive answer to your problem.