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]

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:

Thank you very much!!!!

Glad you got this working and thanks for the feedback.

 

[vanowm]

There is a tiny wrinkle...if excel file was changed but not saved, and user tries close whole window while print preview is opened, it pops a prompt asking to save changes, if hit cancel button, it doesn't switch to default sheet.

So, I've made "cur" variable public:

Public cur As String

and added this into workbook:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Debug.Print "cur = """ & cur & """"
    Sheets(cur).Select
End Sub

But it fails because "cur" variable is empty?

Any suggestions?

Thanks.

 

[Jaafar Tribak]

But it fails because "cur" variable is empty?

Any suggestions?

Thanks.

Just tried that and I could replicate the problem ... let me see if I can find a fix.

 

[Jaafar Tribak]

Instead of assigningg the worksheet name to a varaible, assign it to a temp name and amend the code as follows :

In a Standard Module :

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


Sub Test()
    
    Names.Add "CurrentSheet", ActiveSheet.Name, False
    
    Sheets("Print").Select
        
    Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"
    
    Call Start_PrintPreviewAndPrint_CloseEventListener

    'rest of your code here...

End Sub


Private Sub Start_PrintPreviewAndPrint_CloseEventListener()
    Do
        DoEvents
    Loop Until FindWindowEx(Application.Hwnd, 0, "FullpageUIHost", vbNullString) = 0
    Call SelectSheet
End Sub

Sub Auto_Close()
    Call SelectSheet
End Sub

Sub SelectSheet()
    
    Dim oName As Name
    
    On Error Resume Next
        Set oName = Names("CurrentSheet")
        If Err.Number = 0 Then
            Sheets([CurrentSheet]).Select
            Names("CurrentSheet").Delete
            Debug.Print "done"
        End If
    On Error GoTo 0
End Sub

 

[vanowm]

Thank you very much, it's working perfectly now!

P.S.
Any ideas why these public variables are empty in Workbook_BeforeClose and only when closing window from print preview, if closing with the sheet is opened, it works fine?

 

[Jaafar Tribak]

P.S.
Any ideas why these public variables are empty in Workbook_BeforeClose and only when closing window from print preview, if closing with the sheet is opened, it works fine?

No. The variables are reset and their contents are released not becuase of the print preview window closing ... This happens because the main excel window is closing while there is a loop running .

If you close the Print preview from the close window on the left (which closes the workbook only not the excel application) or if you don't have a loop running then the variables are not reset.

Don't know why this happens apparently if you have a loop running and you try to close the excel application then the vbaproject is reset just like pressing the Break or Reset buttons in the VBE or executing the END statement... at least in excel 2016

 

[Jaafar Tribak]

An alternative to using a timer or a loop both of which can be problematic , you could use the commandbars OnUpdate event as follows :

Note: This code must go in a class module such as the ThisWorkbook module :

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 WithEvents cmndbars As CommandBars

Private cur As String


Sub Test()

    cur = ActiveSheet.Name
    
    Sheets("Print").Select
        
    Application.CommandBars.ExecuteMso "PrintPreviewAndPrint"
    
    Call Start_PrintPreviewAndPrint_CloseEventListener
    

    'rest of your code here...

End Sub


Private Sub Start_PrintPreviewAndPrint_CloseEventListener()
    Set cmndbars = Application.CommandBars
    Call cmndbars_OnUpdate
End Sub


Private Sub cmndbars_OnUpdate()
    If FindWindowEx(Application.hwnd, 0, "FullpageUIHost", vbNullString) = 0 Then
        Sheets(cur).Select
        Set cmndbars = Nothing
        Debug.Print "done"
    End If
End Sub

 

[vanowm]

Very clever. Thank you!

Any reason to have Call cmndbars_OnUpdate line? It seems to work without it.

P.S.
One little thing that I'm able do with "loop" method is to prevent print sheet from showing on screen by using Application.ScreenUpdating = False before switching to "print" sheet. This method doesn't seem to work with event method though.

 

[Jaafar Tribak]

Very clever. Thank you!

Any reason to have Call cmndbars_OnUpdate line? It seems to work without it.

P.S.
One little thing that I'm able do with "loop" method is to prevent print sheet from showing on screen by using Application.ScreenUpdating = False before switching to "print" sheet. This method doesn't seem to work with event method though.

I called cmndbars_OnUpdate just as an extra safety step .

Where exactly in your code do you have the Application.ScreenUpdating=False ?

When I run the Test Sub using either the loop or OnUpdate methods, the "Print" sheet doesn't show up despite not turning screenupdate off.

 

[vanowm]

Just above Sheets("Print").Select

I can see for a split of a second the print sheet before it switches to default one. And when "save changes" prompt shown, then I can see print sheet on the background too. With loop print sheet not visible at all. It's possible something wrong with my excel, because some things is very slow