print dialog box

Sub PrintTest()

    Dim i As Long
    
    For i = 1 To 10
        Hide_NowPrinting_Window = True
        ActiveSheet.PrintOut
        [B][COLOR=Green]'notice how the range updates ( no screen freezing )[/COLOR][/B]
        Range("a1") = Range("a1") + 1
    Next i

End Sub

Option Explicit

Private Declare Function FindWindow Lib "user32.dll" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String) As Long
 
Private Declare Function GetWindowLong Lib "user32" _
Alias "GetWindowLongA" ( _
ByVal hwnd As Long, _
ByVal nIndex As Long) As Long

Private Declare Function SetWindowLong Lib "user32" _
Alias "SetWindowLongA" _
(ByVal hwnd As Long, _
ByVal nIndex As Long, _
ByVal lNewLong 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 hHook 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 Const WH_CBT As Long = 5
Private Const GWL_HINSTANCE As Long = (-6)
Private Const HCBT_CREATEWND = 3
Private Const GWL_WNDPROC As Long = (-4)
Private Const WM_DESTROY As Long = &H2
Private Const WM_NCPAINT As Long = &H85

Private Const NOW_PRINTING_WND_CLASS_NAME As String = "bosa_sdm_XL9"
Private lCBTHook  As Long
Private lPrevWnProc As Long
Private lESCkey As Long
Private bCurrentlyPrinting As Boolean



Public Property Let Hide_NowPrinting_Window(Hidden As Boolean)

    'make sure there is somthing to print
    'to avoid raising an error while the hook is set.
    If WorksheetFunction.CountA(ActiveSheet.UsedRange) <> 0 Then
         If Hidden And Not bCurrentlyPrinting Then
            bCurrentlyPrinting = True
            'Temporarly disable the ESC key .
            lESCkey = 1
            Call RegisterHotKey(0&, lESCkey, 0&, vbKeyEscape)
            'Temporarly hook the excel process to watch
            'for the 'Now Printing' Window.
            lCBTHook = SetWindowsHookEx(WH_CBT, AddressOf CBTProc, _
            GetAppInstance, GetCurrentThreadId)
        End If
    End If

End Property



Private Function CBTProc _
(ByVal idHook As Long, ByVal wParam As Long, _
 ByVal lParam As Long) As Long
 
    Dim sBuffer As String
    Dim lRetVal As Long
 
    Select Case idHook
    
        Case Is = HCBT_CREATEWND
        ' Some Wnd has been created within the excel process.
        sBuffer = Space(256)
        lRetVal = GetClassName(wParam, sBuffer, 256)
          'Is it our 'Now Printing' wnd ?
        If Left(sBuffer, lRetVal) = NOW_PRINTING_WND_CLASS_NAME Then
        'if so subclass it now.
        lPrevWnProc = SetWindowLong _
        (wParam, GWL_WNDPROC, AddressOf CallBack)
        End If
        
        'done with hook.
        UnhookWindowsHookEx lCBTHook
    
    End Select
    
    'Call next hook if any.
    CBTProc = CallNextHookEx _
    (lCBTHook, idHook, ByVal wParam, ByVal lParam)
 
End Function
 
 

Private Function CallBack _
(ByVal hwnd As Long, ByVal Msg As Long, _
ByVal wParam As Long, ByVal lParam As Long) As Long
 
    Select Case Msg
    
        Case WM_NCPAINT
            'If the 'Now Printing' Wnd is being redrawn
            'Disable it and hide it.
            EnableWindow hwnd, 0
            ShowWindow hwnd, 0
     
        Case WM_DESTROY
        
            'The Printing finishes here
            'so unsubclass the wnd and restore the ESC key.
            Call UnregisterHotKey(0, lESCkey)
            Call SetWindowLong(hwnd, GWL_WNDPROC, lPrevWnProc)
            bCurrentlyPrinting = False
           
   End Select
    
    CallBack = CallWindowProc _
    (lPrevWnProc, hwnd, Msg, wParam, ByVal lParam)
 
End Function


Private Function GetAppInstance() As Long
 
    GetAppInstance = GetWindowLong _
    (FindWindow("XLMAIN", Application.Caption), GWL_HINSTANCE)
 
End Function