How to prevent the Printing dialog box from displaying RRS feed

  • Question

  • I am using Excel 2010, 32-bit on Windows 8.

    I am running a Year-End procedure in which some sheets are printed via the VBA .printout method. All works fine, but I would prefer not to have the Printing dialog box show. ( I am calling it a dialog box because it displays some dialog, but it is not interactive. It only displays what it is doing). Turning off ScreenUpdating and DisplayAlerts makes no difference. Any help would be much appreciated.

    Best regards.

    Sunday, November 24, 2013 2:57 PM

All replies

  • Re:  Prevent dialog box display

    Yes, there is a way by calling two Windows functions.
    It is serious stuff however, as it must be properly undone or a reboot becomes necessary.
    I doubt if it will function on 64 bit Windows and nothing much at all seems to work on XL2013, so...

    (do not use the quote marks shown wrapped around the code)
    Declare the two following functions at the top of the module that contains the print code, just under Option Explicit...
       "Private Declare Function GetDesktopWindow Lib "user32" () As Long"
       "Private Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As Long) As Long"

    Before printing make the ApI call to lock the window...
       "LockWindowUpdate GetDesktopWindow"

    After printing, undo it...
       "LockWindowUpdate False"

    Make sure that all code exit points include the undo code and
    use error handling in your code that also includes the undo code.
    You have been warned.

    Jim Cone
    Portland, Oregon USA (Dropbox)
    (free & commercial excel add-ins & workbooks)

    • Edited by James Cone Tuesday, October 25, 2016 1:36 AM
    Wednesday, November 27, 2013 11:58 PM