A CHALLENGE for the gurus

[tmktech]

Hello gurus! First of all, OHARAB sent a solution under another thread that was very involved, but I'm willing to do if necessary, BUT I ALWAYS PREFER THE TIGHTEST CLEANEST CODE POSSIBLE. T H E R E ' S G O T T O B E A B E T T E R W A Y ! ! !

Here's my scenario:

I've got a VBA function (invoked from EXCEL) which generates several different output mediums for numerous entities. I've recently employed "EnableCancelKey" to trap the escape key (1) preventing 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 because if escape if enter whil the dialog is up (or the cancel button is clicked), my code is interrupted. IS THERE ANYWAY TO SHUT THIS PRINTING DIALOG OFF??? I've scanned the online world and have only seen the same suppress code supplied by Ben Oharab.

FYI - Application.DisplayAlerts = False does not work. Nor do the follwing:

CancelKeyEnabled = xlerrorhandler
application.interface = false

Thanks in advance!!!

 

[aMember]

Private Sub Workbook_BeforePrint(Cancel As Boolean)

'turn off events so you don't run out of memory
Application.EnableEvents = False

Call myPrintRoutine
Cancel = True

Application.EnableEvents = True

end sub

 

[tmktech]

Thanks for the feedback, aMember!

Question. If the event is the BEFORE print procedure, it must have been invoked from a .Printout (or other print command) elsewhere in the code (right?) How can I call the print routine from within an event that is invoked by a print routine / command elsewhere?

 

[aMember]

Because EnableEvents=False will prevent the Before_Print from being called so that you can control the print.

Did you try it? Was this what you were looking for or did I miss the boat?

My test was:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

'turn off events so you don't run out of memory
Application.EnableEvents = False

Call myPrintRoutine
Cancel = True

Application.EnableEvents = True

end sub

sub myPrintRoutine()
activesheet.printout
end sub

 

[tmktech]

I did try it. And it definitely shut off the print dialog but it also suppressed printing. The cancel = true is the culprit. And as you probably know, removing that code brings back the dialog along with the print.

Did you get output? (I used your exact code).

 

[aMember]

Yes, several times I tried it and got output.
I tried it from File-Print and from another routine with a ".printout" statement.
I'll check my code again. What I posted earlier was from memory as I had already closed excel. I'll try and repeat it.
Oh, one time it didn't print. The problem was I had an empty active sheet.

 

[aMember]

Hmmm...I know I did this yesterday differently but can't remember what I did so here's my shot today:

-----------------------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'This suppresses printing from
'File-Print (or clicking on the print icon)

Cancel = True

End Sub

Sub myPrintRoutine()
' Use to print from VBA
Application.EnableEvents = False
ActiveSheet.PrintOut
Application.EnableEvents = True
End Sub


Does that help?

 

[tmktech]

No, that won't do it either. The .EnableEvents = False in the print routine prevents the BeforePrint code from happening, so the cancel = true is moot. Removing the .enableevents = false opens up the event but again, the canel = true kills any printing.

FYI - I'm using Windows / Office 2K, and I am using the .printout in my routine.

Thanks for your continued help!

 

[aMember]

No, the enableEvents=false prevents the BeforePrint from being executed.

But the cancel = true in BeforePrint IS executed if the user selects FILE->PRINT.

So, if you do
sub SomeRoutine
...code here...
call myPrintRoutine
... more code here...
end sub
you will get output.

However, if while having the WB open, the user clicks on the print icon, BeforePrint is executed and will not print.

 

[tmktech]

Sorry, amember. Maybe I'm being thick here. I'm trying to prevent the print icon from WITHIN a VBA routine via the .printout method, not from an explicit FIle->Print .

AM I not getting your point???

 

[aMember]

LOL...one of us isn't...and it is probably me.

>> I'm trying to prevent the print icon from WITHIN a VBA

not "print icon", you mean the print dialog box...you want to prevent the print dialog box from appearing when you do .printout, right?
That is what this code did for me. Wait, what version of Excel are you running? I'm using Excel '97.

 

[tmktech]

I guess you missed that in a prev. reply. I'm using Office 2000. In my case it's all or nothing - either BOTH printing and the printing dialog and NEITHER (based on the cancel = ) setting in the BeforePrint.

Thanks!!!!!