Message box over print preview

[cshetty]

Dear Experts

I have a VBA to preview before printing.
My requirement is to display the message box over the print preview not after closing the preview.

When I select Yes, preview screen should disappear and Print
When I select No, preview screen should disappear and go to the selected sheet.

Thank you in advance.

Sub PrintChq()
Dim ans As Boolean
Dim answer As Integer
'
' PrintChq Macro

    Dim strOldPrinter As String

    strOldPrinter = Application.ActivePrinter
    Application.ScreenUpdating = False
    Sheets("Sheet2").Select
    ActiveSheet.PrintPreview
    answer = MsgBox("Yes / No", vbQuestion + vbYesNo + vbDefaultButton2, "Continue Printing Cheque ?")
    If answer = vbYes Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, ActivePrinter:="HP LaserJet Professional P1102"
        Application.ActivePrinter = strOldPrinter
        Sheets("Sheet1").Select
        Application.ScreenUpdating = True
    Else
        MsgBox "Print Cheque Cancelled"
    End If
End Sub

 

[Micron]

You don't say what is happening, but it seems counterintuitive to turn off screen updating when you're wanting to see the effects of the code. Perhaps see what happens when you don't do that?

 

[cshetty]

You don't say what is happening, but it seems counterintuitive to turn off screen updating when you're wanting to see the effects of the code. Perhaps see what happens when you don't do that?

Thank you, Micron, for the reply.

When the macro runs, i get the print preview screen. Once i close the preview screen i get the message box displayed for confirmation. If Yes is selected, it prints and if No is selected it aborts printing. Is there any way I get the message box on the print preview screen? I have tried with enabling screen update, but no avail. What exactly I want is When selected Yes, the preview screen should disappear and print. Same in the case of selecting No, preview screen should disappear and abort printing.

There is one more issue, Once the preview screen appears, if i press Esc button, macro gets stuck, and i have to end task.

Hope I was able to explain the issue properly.

Thanks in advance for any resolution.

 

[Jaafar Tribak]

Displaying the message box will prevent you from interacting with the PrintPreview Ribbon commands because the message box is a modal window.

 

[cshetty]

Displaying the message box will prevent you from interacting with the PrintPreview Ribbon commands because the message box is a modal window.

Thank you Jaafar for this info.

 

[Jaafar Tribak]

Thank you Jaafar for this info.

Why can't you use the the Print and Close Print Preview Ribbon buttons ?

 

[cshetty]

Why can't you use the the Print and Close Print Preview Ribbon buttons ?

I get your point.. When I personally do close the preview screen. But I have problem with other users, who are not familiar with this, they tend to use Esc button and macro gets stuck forcing to End the task. I tried disabling the Esc option. Still it gives me problem sometimes.

Sub PrintChq()
' Dim ans As Boolean
Dim answer As Integer
'
' PrintChq Macro

    Dim strOldPrinter As String
    Application.EnableCancelKey = xlDisabled
    strOldPrinter = Application.ActivePrinter
    ' Application.ScreenUpdating = False
    Sheets("Sheet2").Select
    ActiveSheet.PrintPreview
    answer = MsgBox("Yes / No", vbQuestion + vbYesNo + vbDefaultButton2, "Continue Printing Cheque ?")
    If answer = vbYes Then
        ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, ActivePrinter:="HP LaserJet Professional P1102"
        Application.ActivePrinter = strOldPrinter
        Sheets("Sheet1").Select
        ' Application.ScreenUpdating = True
    Else
        MsgBox "Print Cheque Cancelled"
    End If
    Application.EnableCancelKey = xlInterupt
End Sub

 

[Jaafar Tribak]

I see.
Application.EnableCancelKey = xlDisabled won't work . Just tried Application.OnKey "{ESC}", "" and it doesn't work either.
Let me try something else and see what happens .

 

[Jaafar Tribak]

Try this custom DisableEscapeKey Property . This should prevent users from using the ESC key while the PrintPreview window is on display. No message box required.

The issue now is that this doesn't allow you to select the number of copies or Printer name. But then, why would you need that if you are already giving the user the choice to select the required printing settings from the PrintPreview window.

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function RegisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long) As Long
#Else
    Declare Function RegisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Declare Function UnregisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long) As Long
#End If
 
Sub PrintChq()
    Sheets("Sheet2").Select
    DisableEscapeKey = True
    ActiveSheet.PrintPreview
    DisableEscapeKey = False
End Sub

Private Property Let DisableEscapeKey(ByVal bDisable As Boolean)
    If bDisable Then
        Call RegisterHotKey(Application.hwnd, 1&, 0&, VBA.vbKeyEscape)
    Else
        Call UnregisterHotKey(Application.hwnd, 1&)
    End If
End Property

 

[cshetty]

Try this custom DisableEscapeKey Property . This should prevent users from using the ESC key while the PrintPreview window is on display. No message box required.

The issue now is that this doesn't allow you to select the number of copies or Printer name. But then, why would you need that if you are already giving the user the choice to select the required printing settings from the PrintPreview window.

Option Explicit

#If VBA7 Then
    Declare PtrSafe Function RegisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long) As Long
#Else
    Declare Function RegisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Declare Function UnregisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long) As Long
#End If
 
Sub PrintChq()
    Sheets("Sheet2").Select
    DisableEscapeKey = True
    ActiveSheet.PrintPreview
    DisableEscapeKey = False
End Sub

Private Property Let DisableEscapeKey(ByVal bDisable As Boolean)
    If bDisable Then
        Call RegisterHotKey(Application.hwnd, 1&, 0&, VBA.vbKeyEscape)
    Else
        Call UnregisterHotKey(Application.hwnd, 1&)
    End If
End Property

Let me try this...

This VBA is to print on preprinted stationary form which has restricted length of characters (amount in words) in a cell and there should not be any word wrapping. The preview is just to check there is no wrapping of the text in a cell. If there is wrapping user have to adjust the length of the text (for which there is a separate control, which works perfect)