Macro not to display Print Dialog

[howard]

I have the following macro below

I do not want to display the print dialog button when Printing

 Sub Print_Payment_advices()
Dim lr As Long, I As Long
    For I = Sheets("payment advice BR1").Index To Worksheets.Count
Application.DisplayAlerts = False
Application.ScreenUpdating = False
 
        With Worksheets(I)
           If Not .Range("C9") = "" Then .PrintOut
      
        End With
          Next
          Application.DisplayAlerts = True
Application.ScreenUpdating = True

End Sub

It would be appreciated if someone could kindly amend my code

i have attached a screen print showing the print dialog box, I need supressed

Suppress Print Dialog.docx

Shared with Dropbox

www.dropbox.com

 

[Jaafar Tribak]

This was discussed in this thread

 

[howard]

thanks for the link

 

[Jaafar Tribak]

thanks for the link

So, what happened ? did the code work for you ?
BTW, are you using excel xl32bit or x64bit ?

 

[howard]

The code works perfectly

I am using XL32 Bit

 

[Jaafar Tribak]

this code in the linked thread didn't work for me and in my opinion it can potentially freeze the entire desktop specially when printing is not directed to a physical printer and the user is prompted with the "Save Print Output As" dialog for printing to a file. That code crashed my application a couple of times.

I recommend using the following alternative code which does not block the desktop screen updating and therefore doesn't carry the risk of freezing and crashing excel... The code is also updated so that it works in Excel x32bit and x64bit.



In a Standard Module:

Option Explicit
 
#If VBA7 Then
    #If Win64 Then
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongPtrW" (ByVal hwnd As LongLong, ByVal nIndex As Long, ByVal dwNewLong As LongLong) As LongLong
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongPtrA" (ByVal hwnd As LongLong, ByVal nIndex As Long) As LongLong
    #Else
        Private Declare PtrSafe Function SetWindowLong Lib "user32" Alias "SetWindowLongW" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
        Private Declare PtrSafe Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    #End If
    Private Declare PtrSafe Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As LongPtr, ByVal hwnd As LongPtr, ByVal Msg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As LongPtr
    Private Declare PtrSafe Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As LongPtr, ByVal hmod As LongPtr, ByVal dwThreadId As Long) As LongPtr
    Private Declare PtrSafe Function CallNextHookEx Lib "user32" (ByVal hKBhook As LongPtr, ByVal ncode As Long, ByVal wParam As LongPtr, lParam As Any) As LongPtr
    Private Declare PtrSafe Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As LongPtr) As LongPtr
    Private Declare PtrSafe Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare PtrSafe Function GetClassName Lib "user32.dll" Alias "GetClassNameA" (ByVal hwnd As LongPtr, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare PtrSafe Function EnableWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal fEnable As Long) As Long
    Private Declare PtrSafe Function ShowWindow Lib "user32" (ByVal hwnd As LongPtr, ByVal nCmdShow As Long) As Long
    Private Declare PtrSafe Function RegisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Private Declare PtrSafe Function UnregisterHotKey Lib "user32" (ByVal hwnd As LongPtr, ByVal id As Long) As Long
   
    Private lCBTHook  As LongPtr, lPrevWnProc As LongPtr
#Else
    Private Declare Function SetWindowLong Lib "user32" Alias "SetWindowLongW" (ByVal hwnd As Long, ByVal nIndex As Long, ByVal dwNewLong As Long) As Long
    Private Declare Function GetWindowLong Lib "user32" Alias "GetWindowLongA" (ByVal hwnd As Long, ByVal nIndex As Long) As Long
    Private Declare Function CallWindowProc Lib "user32" Alias "CallWindowProcA" (ByVal lpPrevWndFunc As Long, ByVal hwnd As Long, ByVal Msg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Private Declare Function SetWindowsHookEx Lib "user32" Alias "SetWindowsHookExA" (ByVal idHook As Long, ByVal lpfn As Long, ByVal hmod As Long, ByVal dwThreadId As Long) As Long
    Private Declare Function CallNextHookEx Lib "user32" (ByVal hKBhook As Long, ByVal ncode As Long, ByVal wParam As Long, lParam As Any) As Long
    Private Declare Function UnhookWindowsHookEx Lib "user32" (ByVal hHook As Long) As Long
    Private Declare Function GetCurrentThreadId Lib "kernel32" () As Long
    Private Declare Function GetClassName Lib "user32.dll" Alias "GetClassNameA" (ByVal hwnd As Long, ByVal lpClassName As String, ByVal nMaxCount As Long) As Long
    Private Declare Function EnableWindow Lib "user32" (ByVal hwnd As Long, ByVal fEnable As Long) As Long
    Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long
    Private Declare Function RegisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long, ByVal fsModifiers As Long, ByVal vk As Long) As Long
    Private Declare Function UnregisterHotKey Lib "user32" (ByVal hwnd As Long, ByVal id As Long) As Long
   
    Private lCBTHook  As Long, lPrevWnProc As Long
#End If


Private bCurrentlyPrinting As Boolean


Public Property Let Hide_NowPrinting_Window(Hidden As Boolean)

    Const WH_CBT As Long = 5
   
        If Hidden And Not bCurrentlyPrinting Then
            bCurrentlyPrinting = True
            Call RegisterHotKey(0, &HBFFF&, 0, vbKeyEscape)
            lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, 0, GetCurrentThreadId)
        End If

End Property


#If Win64 Then
    Private Function CBTProc( _
            ByVal idHook As Long, _
            ByVal wParam As LongLong, _
            ByVal lParam As LongLong _
        ) As LongLong
#Else
    Private Function CBTProc( _
            ByVal idHook As Long, _
            ByVal wParam As Long, _
            ByVal lParam As Long _
        ) As Long
#End If
 
    Const HCBT_CREATEWND = 3
    Const GWL_WNDPROC As Long = (-4)
    Dim sBuffer As String * 256, lRetVal As Long
 
    Select Case idHook
        Case Is = HCBT_CREATEWND
            lRetVal = GetClassName(wParam, sBuffer, 256)
            If Left(sBuffer, lRetVal) = "bosa_sdm_XL9" Then
                lPrevWnProc = SetWindowLong(wParam, GWL_WNDPROC, AddressOf CallBack)
            End If
            Call UnhookWindowsHookEx(lCBTHook)
    End Select
    CBTProc = CallNextHookEx(lCBTHook, idHook, ByVal wParam, ByVal lParam)
 
End Function

#If Win64 Then
    Private Function CallBack( _
            ByVal hwnd As LongLong, _
            ByVal Msg As Long, _
            ByVal wParam As LongLong, _
            ByVal lParam As LongLong _
        ) As LongLong
#Else
    Private Function CallBack( _
            ByVal hwnd As Long, _
            ByVal Msg As Long, _
            ByVal wParam As Long, _
            ByVal lParam As Long _
        ) As Long
#End If

    Const WM_DESTROY As Long = &H2
    Const WM_NCPAINT As Long = &H85
    Const GWL_WNDPROC As Long = (-4)
   
    Select Case Msg
        Case WM_NCPAINT
            Call EnableWindow(hwnd, 0)
            Call ShowWindow(hwnd, 0)
        Case WM_DESTROY
            Call UnregisterHotKey(0, &HBFFF&)
            Call SetWindowLong(hwnd, GWL_WNDPROC, lPrevWnProc)
            bCurrentlyPrinting = False
    End Select
   
    CallBack = CallWindowProc(lPrevWnProc, hwnd, Msg, wParam, ByVal lParam)
 
End Function

USAGE :

Sub Test()
    Hide_NowPrinting_Window = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1
End Sub

 

[howard]

Thanks for the updated Code. It works perfectly