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

vanowm

New Member
Joined
Jun 22, 2019
Messages
11
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
Code:
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:

Code:
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.
 
Last edited by a moderator:

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.
Office 2019

Give this hack a try and see if it works for you:
Code:
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.
 
Upvote 0
am I missing something in the question ?

Code:
Sub PreviewPrint()
    Sheets("NameOfSheet").PrintPreview
End Sub
 
Last edited:
Upvote 0
@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.
 
Upvote 0
@Jaafar Tribak
Thank you. it works when I cancel the print preview, and crashes entire excel when I try to print

How about this :
Code:
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
 
Last edited:
Upvote 0
No crashes now, but it also prints current sheet, instead of "print" sheet.
Strange! It works for me just fine.


EDIT:
How about :
Code:
Application.OnTime Now + TimeSerial(0, 0, 1), "Start_PrintPreviewAndPrint_CloseEventListener"
 
Last edited:
Upvote 0
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:
Code:
Application.OnTime Now + TimeSerial(0, 0, 1), "[COLOR=#ff0000]Module1.[/COLOR]Start_PrintPreviewAndPrint_CloseEventListener"

Thank you very much!!!!
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top