Code for "Close Print Preview"

[Formula11]

When I open a workbook, I want the active sheet to show Print Preview, and then to close it.
I can't find anything on how to close it.
Sending keys to Escape threw an error.

Private Sub Workbook_Open()
    ActiveWindow.SelectedSheets.PrintPreview
    'Something to close Print Preview
End Sub

 

[Jaafar Tribak]

This worked for me:

SendKeys "{ESCAPE}"
ActiveWindow.SelectedSheets.PrintPreview

But what's the point of showing PrintPreview and immediatly closing it ?!

 

[Formula11]

Thanks for looking into it.
It doesn't work for me though, the macro stops at the preview, without escaping.
The point is that he document I'm working on is constantly opened and closed, and I'd like to know where the bounds of the page are, so I don't for example write a table which straddles pages.

 

[Jaafar Tribak]

If that doesn't work, try this alternative and see how it goes :

Note: adjust the value of the Close_In_HowMany_Seconds_FromNow argument to set the time (in seconds) when you want the Print Preview window to be closed.


Place the following code In a Standard Module and call the Test routine:

Option Explicit

#If VBA7 Then
    Private Declare PtrSafe Function SetTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal nIDEvent As Long, ByVal uElapse As Long, ByVal lpTimerFunc As LongPtr) As Long
    Private Declare PtrSafe Function KillTimer Lib "user32" (ByVal hwnd As LongPtr, ByVal uIDEvent As Long) As Long
#Else
    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 uIDEvent As Long) As Long
#End If
 
 
Sub Test()
    Call ClosePrintPreview(Close_In_HowMany_Seconds_FromNow:=0) '<== Close PPreview in 0 sconds - Change argument value to suit
    ActiveWindow.SelectedSheets.PrintPreview
End Sub


Private Sub ClosePrintPreview(ByVal Close_In_HowMany_Seconds_FromNow As Long)
    Call SetTimer(Application.hwnd, 0, Abs(Close_In_HowMany_Seconds_FromNow) * 1000, AddressOf CloseNow)
End Sub

Private Sub CloseNow()
    With Application
        Call KillTimer(.hwnd, 0)
        If .CommandBars.GetEnabledMso("PrintPreviewClose") Then
            .CommandBars.ExecuteMso ("PrintPreviewClose")
        End If
    End With
End Sub

 

[Formula11]

Thanks, that looks like a lot of effort put into this.
I didn't expect it to be so complicated.
The code does actually work.

The 2 lines after "#If VBA7" are marked in red, but it still seems to work.
Cheers.

 

[Jaafar Tribak]

Thanks, that looks like a lot of effort put into this.
I didn't expect it to be so complicated.
The code does actually work.

The 2 lines after "#If VBA7" are marked in red, but it still seems to work.
Cheers.

The PrintPreview is a modal window that halts all subsequent code so the code closing the window needs to be ran asynchronously.

Don't worry about the lines in red. You can leave those lines as they are as the compiler will ignore them.

 

[Formula11]

OK thanks for advising.