Macro to close workbook when file print is selected

  • Thread starter Thread starter Phil
  • Start date Start date
P

Phil

Good morning; I am using Windows 2000 and Excel 2000.
I have the following macro for a read only file. The
Workbook file contains two worksheets. I need to prevent
users from printing either sheet since they are extremely
large, (2,000 pages). The macro is being executed but the
file still prints. What am I missing here?
Thanks
Phil

If ActiveWindow.SelectedSheets.PrintOut = True Then
MsgBox ("This File Is over 1,900 Pages and Too Large to
Print")
ActiveWorkbook.Close
End If
End Sub
 
By the time your code tries to execute, the print has long since spooled off
to the queue. By then it's too late to intercept.

Are you using the BeforePrint event to capture the Print attempt? That's
the appropriate place to do so. Insert the line Cancel = True to abort
prints BEFORE they get to the queue.

In addition, I *think* there's a way to prevent sheets from being printed as
an option, but I may be wrong.

Randall Arnold
 
If you don't want to use VBA
(user can open the file without macro's also)

You can also set the print range on each sheet to a1:a3 or so.
Then when they hit the print button only this cells are printed.
 
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Place this event in the Thisworkbook module and you can't print
as long the user is enabled macro's when opening the file

Right click on the Excel icon next to File in the menubar
Choose view code
paste this event there
Alt-Q to go back to Excel
Save the file
 
Thanks Ron.

What I was not doing was saving the workbook, closing it
and then trying to print after re-opening.
 
Beat me to it!

Of course, he'll want the IF/THEN logic as well, probably. But I'm betting
he can get it from here.

Randall
 
Got it!

I have a nice little message that pops up to inform the
user the file is too large to print.
Thanks much
Phil
 
Back
Top