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

 

[Jaafar Tribak]

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)

So, does that mean the code in my post#9 is what you need or do you still need to prompt the user with the message box so you can set the required number of printout copies and printer device like you have done in your code? and where do you have the seperate control for adjusting the the text wrapping ?

If you still need the messagebox and still want to allow user interaction with the PrintPreview commands, then the only thing I can think of is to use a modeless userform instead of a messagebox.

 

[cshetty]

So, does that mean the code in my post#9 is what you need or do you still need to prompt the user with the message box so you can set the required number of printout copies and printer device like you have done in your code? and where do you have the seperate control for adjusting the the text wrapping ?

If you still need the messagebox and still allow the user interaction with PrintPreview commands, then the only thing I can think of is to use a modeless userform instead of a messagebox.

I need the message box to prompt the user to continue print (if there is no word wrap) or abort (if there is word wrap, so that they can adjust the length and rerun print)

Number of copies not an issue. Its only one copy

I have assigned a particular printer as we have many printers available, but this is the only printer which does the job perfectly. It's a small printer which can handle small sized stationery were well.

I am okay with message box appearing after closing the preview sheet.

Now my challenge is how to incorporate your code for disabling Esc key into my earlier code.

 

[Jaafar Tribak]

Now my challenge is how to incorporate your code for disabling Esc key into my earlier code.

Should be easy enough:

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 PtrSafe Function RegisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long) As Long
#End If

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

    Dim strOldPrinter As String
   
    On Error GoTo Xit

    strOldPrinter = Application.ActivePrinter
    Application.ScreenUpdating = False
    Sheets("Sheet2").Select
    DisableEscapeKey = True
    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
   
Xit:
    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]

Should be easy enough:

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 PtrSafe Function RegisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long) As Long
#End If

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

    Dim strOldPrinter As String
  
    On Error GoTo Xit

    strOldPrinter = Application.ActivePrinter
    Application.ScreenUpdating = False
    Sheets("Sheet2").Select
    DisableEscapeKey = True
    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
  
Xit:
    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

Thanks a ton, Jaafar.

I am going with this code. Works well.

 

[Jaafar Tribak]

Thanks a ton, Jaafar.

I am going with this code. Works well.

You are welcome.