Printing Problems (MS Excel 97/SR2a)

Topic

New Reply

I have a fairly complex spreadsheet that is used to print a multi-page certificate.

The problem is that the workbook has been developed on a Windows 95 PC (yes, that is what they are using) using the following settings; 640×480 with Small Fonts option enabled.

All sheets printed as expected breaking where required.

Then a user changed nothing except the screen resolution to 800×600 with Large Fonts option and the problems started.

Whilst absolutely no changes were made to any MS Excel settings, the pagination went astray with the pages breaking just one row before the previous example and text that was close to the right margin was being truncated.

BTW, this spreadsheet was being printed on a HP laser with MS Excel margins set well within the allowable margins.

Any suggestions as to why this has happened, or how to stop it from happening, would be appreciated.

Thanks, Leigh

Reply | Quote

Replies

Unfortunately, Excel is not WYSIWYG (What You See Is What You Get), and screen resolution and fonts can affect printing. I have found that the only way to fix the problem you are experiencing is to manually adjust the layout with the new settings.

Reply | Quote

Thanks for your response Legare, but this is not an available solution.

The finished certificates are stored on a shared network drive and another user may be required to print it and ‘discovers’ the pagination has gone astray but cannot modify the layout as it is on protected sheets (to provide a consistent layout).

Any other suggestions?

Reply | Quote

What you could try is a BeforePrint event in which you adjust all these various items so it works on every printer in your environment. Get to the BeforePrint event through the Worksheet object in the code Window. I suspect if you focus on the ones in red it may help:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.LeftMargin = Application.InchesToPoints(0.75)
.RightMargin = Application.InchesToPoints(0.75)
.TopMargin = Application.InchesToPoints(1)
.BottomMargin = Application.InchesToPoints(1)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 3
.FitToPagesTall = False
End With
End Sub

… on the other hand, you may not be fix the problem at all.

Reply | Quote

My solution to that problem was to adjust the form to be small enough so that it would print on the system with the most restrictive problem. That left some extra white space around the printout on other systems.

Some other possible solutions:

1- Use VBA code to do the printing. That code would print each page individually, turning on the Fit to 1 Page Wide by 1 Page High.

2- Use VBA code to determine the monitor and printer combination and dynamically modify the layout to fit properly.

3- Replace all hardware and software so that it is the same and the problem goes away.

Sorry, there are no easy answers to this one. In the above, there are negatives to each, like not being able to get page numbers in headers or footers in solution 1, and like the cost of solution 3.

Reply | Quote