Open native (not Sheet.PrintPreview) print preview window for non-active sheet?

[vanowm]

Hello.

I'm trying open "native" print preview window (the same window that opens when clicked print button in toolbar) for a specific sheet.
The only command I know that can do this is

Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"

But I'm having problem open it for a specific sheet and stay on original sheet when the window is closed:

cur = ActiveSheet.Name
Sheets("Print").Select
'ActiveSheet.PrintPreview 'shows correct sheet
Application.CommandBars.ExecuteMso "PrintPreviewAndPrint" 'shows incorrect sheet
Sheets(cur).Select

The problem with this code is that print preview loads active sheet AFTER the rest of the code was executed.
So maybe it needs some kind of a "preview window closed" event listener and then switch to "original" sheet?

Any suggestions?

Thank you.

 

[Jaafar Tribak]

Which edition of excel are you using ?

 

[vanowm]

Office 2019

 

[Jaafar Tribak]

Office 2019

Give this hack a try and see if it works for you:

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As LongPtr
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As LongPtr) As Long
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function SetTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
    Private Declare Function KillTimer Lib "user32" (ByVal hwnd As Long, ByVal nIDEvent As Long) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private cur As String


Sub Test()

    cur = ActiveSheet.Name
    
    Sheets("Print").Select
    
    [B][COLOR=#0000ff]Call Start_PrintPreviewAndPrint_CloseEventListener[/COLOR][/B]
    
    Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"
    
   [COLOR=#008000] 'rest of your code here...[/COLOR]

End Sub


Private Sub Start_PrintPreviewAndPrint_CloseEventListener()
    SetTimer Application.hwnd, 0, 0, AddressOf TimerProc
End Sub


Private Sub TimerProc()
    If FindWindowEx(Application.hwnd, 0, "FullpageUIHost", vbNullString) = 0 Then
        KillTimer Application.hwnd, 0
        Sheets(cur).Select
    End If
End Sub

Regards.

 

[Yongle]

am I missing something in the question ?

Sub PreviewPrint()
    Sheets("NameOfSheet").PrintPreview
End Sub

 

[vanowm]

@Jaafar Tribak
Thank you. it works when I cancel the print preview, and crashes entire excel when I try to print

@Yongle
That command shows buggy and totally different preview window, which I don't want to use.

 

[Jaafar Tribak]

@Jaafar Tribak
Thank you. it works when I cancel the print preview, and crashes entire excel when I try to print

How about this :

Option Explicit

[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If"]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else"]#Else[/URL] 
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
[URL="https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End"]#End[/URL]  If

Private cur As String


Sub Test()

    cur = ActiveSheet.Name
    
    Sheets("Print").Select
        
    Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"
    
[COLOR=#0000ff]    Call Start_PrintPreviewAndPrint_CloseEventListener[/COLOR]

[COLOR=#008000]    'rest of your code here...[/COLOR]

End Sub


Private Sub Start_PrintPreviewAndPrint_CloseEventListener()
    Do
        DoEvents
    Loop Until FindWindowEx(Application.hwnd, 0, "FullpageUIHost", vbNullString) = 0
    Sheets(cur).Select
    Debug.Print "done"
End Sub

 

[vanowm]

No crashes now, but it also prints current sheet, instead of "print" sheet.

 

[Jaafar Tribak]

No crashes now, but it also prints current sheet, instead of "print" sheet.

Strange! It works for me just fine.


EDIT:
How about :

Application.OnTime Now + TimeSerial(0, 0, 1), "Start_PrintPreviewAndPrint_CloseEventListener"

 

[vanowm]

Sorry, my bad, I was testing it directly in my final code instead of your test function and added the blue line above printpreview line...your last suggestion also worked even with my f-up, I just had to prepend my module's name to the function name in that line:

Application.OnTime Now + TimeSerial(0, 0, 1), "[COLOR=#ff0000]Module1.[/COLOR]Start_PrintPreviewAndPrint_CloseEventListener"

Thank you very much!!!!