Differentiate between 'Print' & 'Print Preview' in VBA c

Mattantaliss · Apr 15, 2006

Hello,

I have set up some code in a Workbook_BeforePrint Sub to log information as worksheets are printed. That is, for example, I have sheetA, sheetB, and sheetLog, and whenever a user prints either sheetA or sheetB, my code will take some information (e.g., name and date) from that sheet and copy it to sheetLog.

The issue I have now discovered is that the information will also be logged if the user merely executes a Print Preview, which makes sense to me. I am wondering, though, if there is a way to differentiate, in the code, between an actual 'Print' and just a 'Print Preview'. I am envisioning something like:

Code:

   If <just Print Preview> Then
      <don't log information>
   Else 'actually Printing
      <do stuff as normal>
   End If

Does such a distinction between 'Print' and 'Print Preview' exist?

mortgageman · Apr 15, 2006

The macro recorder generates the following. Macro1() is Print preview
and Macro2() is Print

Gene, "The Mortgage Man", Klein

Option Explicit

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/15/2006 by Gene Klein
'

'
Range("A1:A2").Select
ActiveSheet.PageSetup.PrintArea = "$A$1:$A$2"
ActiveWindow.SelectedSheets.PrintPreview
End Sub
Sub Macro2()
'
' Macro2 Macro
' Macro recorded 4/15/2006 by Gene Klein
'

'
ActiveSheet.PageSetup.PrintArea = "$A$1:$A$2"
Selection.PrintOut Copies:=1, Collate:=True
End Sub

Mattantaliss · Apr 15, 2006

Hmm.... Can you think of any way of testing which method is being executed? My first thought,

Code:

If ActiveWindow.SelectedSheets.PrintPreview = True Then...

will obviously not work, but that's the idea I'm after. Any thoughts?

mortgageman · Apr 16, 2006

Code:
Hmm.... Can you think of any way of testing which method is being executed? My first thought,

Code:

If ActiveWindow.SelectedSheets.PrintPreview = True Then...

will obviously not work, but that's the idea I'm after. Any thoughts?

No. I thought of maybe capturing Copies in a variable (th idea being that if it is > 0 then it is not a preview), but it doesn't seem to be in a memory that is reachable by a user (at least a user as limited as me!)

Gene, "The Mortgage Man", Klein

Mattantaliss · Apr 17, 2006

As a follow-up on this, I got to thinking that if I am unable to tell the difference between a 'Print' and a 'Print Preview', I might be able to just tell when the 'Print Preview' window has closed and then undo any logging that had occurred when it was brought up. Would it be possible to know when that window has closed to have some code undo the logging that had been done? I'm picturing something like:

Code:

<onPrintPreviewClose>
     <delete row(s) of logged info>

Norie · Apr 17, 2006

Mattantaliss

There is no event for that.

I don't really think what you are doing will be possible.

What is the ultimate aim?

I can think of some workarounds. eg replacing the standard print preview with a custom one.

Mattantaliss · Apr 17, 2006

Hi Norie,

My intention is to take some information from a sheet and log it on another sheet whenever a user prints it. However, I found that, with what I have, the information is logged when the user executes a print preview also. So what I'm looking to do is somehow tell the difference between an actual print and a print preview, and then not log information in the case of the latter.

erik.van.geit · Apr 17, 2006

Hello, Mattantaliss,

you could disable print using the menu and so force to use a button
within the code you would do everything you want

WORKBOOK module

Code:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If button_used = False Then
MsgBox "only print with button", 48, "PRINTING CANCELED"
Cancel = True
End If
End Sub

normal module

Code:

Public button_used As Boolean

Sub print_with_button()
button_used = 1
'printcode
'log and do everything you want
button_used = 0
End Sub

BUT: you will not have access to print-preview neighther

Suspecting Rafaaj2000 & RightClick are members which could solve this among others ...

kind regards,
Erik

Jaafar Tribak · Apr 19, 2006

Mattantaliss,

Are you using XL2000 or later ? If so, then you may want to try the following which takes advantage of the Office.CommandBarButton Click event :

1- Add a Class Module to your Project and give the Class the name of PrintPrevClass via the Properties window in the VBE.

Place this code in it :

Code:

Option Explicit

Public WithEvents CtrEvents As CommandBarButton
Private bln_SkipCode As Boolean


Private Sub CtrEvents_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)

    'Always reset this prop flag
    bln_SkipCode = True
    
End Sub


Public Sub execute()

    'hook all instances of the PrintPreview Control
    Set Me.CtrEvents = CommandBars.FindControl(ID:=109)
    
End Sub


'this prop will serve as a global flag
Public Property Get SkipBeforePrintEvent() As Boolean

    SkipBeforePrintEvent = bln_SkipCode
    
End Property


Public Property Let SkipBeforePrintEvent(ByVal vNewValue As Boolean)

    bln_SkipCode = vNewValue
    
End Property

2- Place this code in the WorkBook Module :

Code:

Option Explicit

Dim objPrintPrv As PrintPrevClass


Private Sub Workbook_BeforePrint(Cancel As Boolean)

    Dim strMsg As String
    
    strMsg = "This Message won't display and the subsequent " _
    & vbCrLf & "code won't be executed if On Print Preview Mode "
    'Skip error silently if the PrintPrevClass is not instantiated
    On Error Resume Next
    'check if the calss instance exists
    If Not objPrintPrv Is Nothing Then
        'if so, is it enabled ?
        If objPrintPrv.SkipBeforePrintEvent Then
            'if so, reset prop and get out skipping the
            'before print event code
            GoTo Reset_prop
        End If
    End If
    MsgBox strMsg, vbInformation
    'here goes your "Before_Print" event code....
Reset_prop:
    'Always reset this prop in case a Print Method is requested.
    objPrintPrv.SkipBeforePrintEvent = False

End Sub


Sub Enable_PrintPrevClass()

    If objPrintPrv Is Nothing Then
        Set objPrintPrv = New PrintPrevClass
        objPrintPrv.SkipBeforePrintEvent = True
        objPrintPrv.execute
    End If
    
End Sub


Sub Disable_PrintPrevClass()

    Set objPrintPrv = Nothing
    
End Sub

'\\You can assign the Enable\Disable procedures to the
'\\Open and BeforeClose events of the workbook

Private Sub Workbook_Open()

    Enable_PrintPrevClass
    
End Sub


Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Disable_PrintPrevClass
    
End Sub

Next, just save the WorkBook , close it and reopen it again.

Now, when you try an actual Print, any code in the Before_Print event (ie: Msgbx) will fire as normal but if you try a Print Preview it won't !

You may want to assign the 2 macros Enable_PrintPrevClass\Disable_PrintPrevClass to some Buttons instead of assigning them to the Open\Before_Close events.

Let me know if it works for you.

Regards.

erik.van.geit · Apr 19, 2006

rafaaj2000

For me it was clear you could solve this !!
For me it's working.

Next, just save the WorkBook , close it and reopen it again.

For your information:
did you know you can run the Workbook_Open procedure: you don't need to close and open again

best regards,
Erik

Differentiate between 'Print' & 'Print Preview' in VBA c

Mattantaliss

Board Regular

Excel Facts

mortgageman

Well-known Member

Mattantaliss

Board Regular

mortgageman

Well-known Member

Mattantaliss

Board Regular

Norie

Well-known Member

Mattantaliss

Board Regular

erik.van.geit

MrExcel MVP

Jaafar Tribak

Well-known Member

erik.van.geit

MrExcel MVP

Similar threads

Forum statistics

Share this page

Differentiate between 'Print' & 'Print Preview' in VBA c

Board Regular

Excel Facts

Well-known Member

Board Regular

Well-known Member

Board Regular

Well-known Member

Board Regular

MrExcel MVP

Well-known Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock