Print & Print Preview crashes

[Marion]

Hello, I have created a lovely macro that builds reports and then
emails them out to people, and i am very proud of it. The reports that
go out are perfect except in one respect...

If you try to print or print preview, they crash excel with an error
message about not being able to read from a memory location.

This is all done in Office 2000 and Windows 2000, all our machines
will print and print preview other files (even other sheets in the
same workbook), but all machines resolutely refuse to print these
reports.

the following code sets up the print settings:

' And set up the print size for the recipient
With ActiveSheet.PageSetup
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintArea = ""
.TopMargin =
Application.InchesToPoints(0.590551181102362)
' Repeat the headings on each printed page
.PrintTitleRows = "$1:$7"
.PrintTitleColumns = ""
' And set up the footer
.LeftFooter = "Printed: &D"
.CenterFooter = "Page &P of &N"
.RightFooter = "&Z&F"

there are subtotals on the sheet, and the books are password
protected, but otherwise there is nothing unusual. Any ideas? I am at
the end of my rope!

Thanks

 

[Tom Ogilvy]

Sounds like a print driver problem. Have you tried printing to another
printer (one that uses a different print driver)?

Other than that, try commenting out individual settings sequentially until
you find the setting that, when commented out, prints successfully (if not
in the exact format you want). Then you might find a way to work around it.

 

[Mike H]

Thanks Tom. I have tried commenting out commands and bringing them back in 1
by 1 but any one of them causes the same problem.

If it is a print driver problem and my whole organisation uses the same
printers and drivers, can you think of anyway that I can format printing
without triggering the memory error that causes the crash?

Thanks

 

[Anthony]

I am having the same issue. Printing on works printers etc. is causing
problems, but for me its not on every occasion.

Try clearing the temp folders of the PC.
Complete all calculations before sending the print command (this solved alot
of my issues). The pages I need to print are all loaded when the print
options form is created and not at the time of printing. So nothing but a
page select is done prior to printing out.
unload all userforms directly after using them. I wanted to leave in memory
and unload at end of the workbook but found unloading them reduced the
crashing.

I am still working on the 1 in 10 that now has issues on a random occasion

Anthony