Disabling the "CANCEL" button on Printout Method

[tmktech]

Hello fellow developers! I've got a problem I haven't been able to resolve on my own. Your help is ggreatly appreciated!

Here's my scenario:

I've got a function which generates several different output mediums for numerous entities. I've recently employed "EnableCancelKey" to trap the escape key to (1) prevent the user from interrupting code, and (2) permitting a controlled stop of an ongoing routine.

HOWEVER, when printed and/or PDF outputs are being processed, the "Printing" dialog appears with the CANCEL button. This dialog nullifies my coding above and kills the process. IS THERE ANYWAY TO SHUT THIS PRINTING DIALOG OFF???

FYI - Application.DisplayAlerts = False do not work.

Thanks in advance!!!

 

[PaulTEG]

Have you tried

Application.ScreenUpdating = False

HTH

 

[tmktech]

PaulTEG,

Yes, I've got .ScreenUpdating = false. I've also got .displayalerts = false and .interactive = false, but when the .Printout method is invoked, the "Printing" dialog (with "currently printing abc on xyz&quot appears with the option to cancel.

This is the loophole that gives the user a chance to interrupt in an uncontrolled manner.

Any other thoughts??

 

[PaulTEG]

Hi tmk,

This is a cheeky way of doing it I saw once in a hidden HTML code problem. Don't know if its possible, but it might be worth a try

Create a "glass" screen over the dialog. If there's some way of presenting a splash screen to cover the dialog, and as soon as it pops up to pass focus to the splash, and if it isn't visible they can't click it.

As I say, don't know if its possible, or if the dialog can relinquish control to the splash after it's been called until it has completed

Just a thought

HTH ;P

 

[oharab]

You could try this. It is adapted from the Access code from

http://www.mvps.org/access

though I have not tested it. I am not sure how the ontime thing works, but it may be worth a try!

Copy all the code into a module:

Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassname As String, _
ByVal lpWindowName As Long) As Long

Private Declare Function apiGetClassName Lib "user32" _
Alias "GetClassNameA" _
(ByVal hWnd As Long, _
ByVal lpClassname As String, _
ByVal nMaxCount As Long) _
As Long

Private Declare Function apiGetWindowText Lib "user32" _
Alias "GetWindowTextA" _
(ByVal hWnd As Long, _
ByVal lpString As String, _
ByVal aint As Long) _
As Long

Private Declare Function apiGetLastActivePopup Lib "user32" _
Alias "GetLastActivePopup" _
(ByVal hWndOwnder As Long) _
As Long

Private Declare Function apiShowWindow Lib "user32" _
Alias "ShowWindow" _
(ByVal hWnd As Long, _
ByVal nCmdShow As Long) _
As Long

Private Const MAX_LEN = 255
Private Const GW_HWNDNEXT = 2
Private Const SW_HIDE = 0
Private Const SW_MINIMIZE = 6
Private Const SW_SHOWMINNOACTIVE = 7
Private Const SW_SHOWDEFAULT = 10

Public blnHideBox As Boolean

Sub sWatchForPrint()
On Error GoTo Err_Handler
Dim lnghWndChild As Long
Dim strCaption As String
Dim strClass As String
Dim lngRet As Long
Dim hWndApp As Long
hWndApp = GetHwnd
'Get the last active popup in hWndApp instance
lnghWndChild = apiGetLastActivePopup(hWndApp)
strClass = fGetClassName(lnghWndChild)
strCaption = fGetCaption(lnghWndChild)
'is this the modal window?
If strClass = "#32770" And Trim(strCaption) = "Printing" Then
lngRet = apiShowWindow(lnghWndChild, SW_SHOWMINNOACTIVE)
End If

Exit_Here:
Exit Sub
Err_Handler:
MsgBox "Error #: " & Err.Number & vbCrLf & Err.Description, _
vbCritical + vbOKOnly, "sWatchAccess-Runtime Error"
Resume Exit_Here
End Sub

Private Function fGetClassName(ByVal hWnd As Long) As String
Dim strBuffer As String
Dim lngRet As Long
strBuffer = String$(32, 0)
lngRet = apiGetClassName(hWnd, strBuffer, Len(strBuffer))
If lngRet > 0 Then
fGetClassName = Left$(strBuffer, lngRet)
End If
End Function

Private Function fGetCaption(ByVal hWnd As Long) As String
Dim strBuffer As String
Dim lngRet As Long
strBuffer = String$(MAX_LEN, 0)
lngRet = apiGetWindowText(hWnd, strBuffer, Len(strBuffer))
If lngRet > 0 Then
fGetCaption = Left$(strBuffer, lngRet)
End If
End Function


Function GetHwnd()
GetHwnd = FindWindow("XLMAIN", 0)
End Function


Sub StartWatch()
sWatchForPrint
Static x
Application.OnTime Now + 3.47222222222223E-06, "StartWatch", , blnHideBox
x = Now + 3.47222222222223E-06
End Sub

Sub PrintOut()
blnHideBox = True
StartWatch
'Print Commands Go Here
ActiveSheet.Print
'End of Print Commands
blnHideBox = False
End Sub


Now use the Printout routine to print your sheets, you will need to put in your own print commands to do whatever you do.

Let me know if this works for you.


Ben ----------------------------------------
Ben O'Hara

http://www.RobotParade.co.uk

----------------------------------------

 

[tmktech]

Paul and Oharab,

Thanks for the feedback. I'll give them a try.

In the meantime, isn't there a SIMPLER solution?! Is there a switch somewhere that I'm missing?