Difference between print and print preview events in excel vba

20,651

I don't think there is a neat way to determine if the event is a print preview or print request.

The solution below is not particularly neat and inconveniences the user slightly, but it works.

The code cancels the event and then prompts the user, based on their response it displays the print preview or prints.

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim Print_or_Preview As XlYesNoGuess

Application.EnableEvents = False

    Cancel = True
    Print_or_Preview = MsgBox("Show Print Preview?", vbYesNo)

    If Print_or_Preview = True Then
        ActiveWindow.ActiveSheet.PrintPreview
        Else
        ActiveWindow.ActiveSheet.PrintOut
    End If
Application.EnableEvents = True

End Sub
Share:
20,651
a_m0d
Author by

a_m0d

I enjoy programming in C++, and also know C, Java, python, php and x86 asm. The various projects that I have / am working on include (these are all just for fun and to explore the capabilities of different languages / frameworks): raytracer object oriented OS in C++ 2d game engine CAS (computer algebra system) which incorporates elements of compilers / interpreters I really enjoy contributing to http://latex.codecogs.com/png.download?%5cfrac%7bStack%7d%7bFlow%7d

Updated on April 25, 2022

Comments

  • a_m0d
    a_m0d about 2 years

    I have some code which intercepts the Before_Print event in excel to make sure that the user has filled in all the required fields before they print the sheet. However, I only want this code to fire when the user is actually printing, not when they are just calling the print preview.

    Is there any way to tell in the Before_Print code whether the user is actually printing or just previewing?

    The code that I currently have is (event stub was generated by excel):

    Private Sub Workbook_BeforePrint(Cancel As Boolean)
        If Not Sheet2.CheckAllFieldsFilled Then
            Cancel = True
        End If
    End Sub