Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Printing Problems (MS Excel 97/SR2a)

    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; 640x480 with Small Fonts option enabled.

    All sheets printed as expected breaking where required.

    Then a user changed nothing except the screen resolution to 800x600 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

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Problems (MS Excel 97/SR2a)

    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.
    Legare Coleman

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Australia
    Posts
    245
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Problems (MS Excel 97/SR2a)

    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?

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Printing Problems (MS Excel 97/SR2a)

    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
    <font color=red>.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)</font color=red>
    .CenterHorizontally = False
    .CenterVertically = False
    .Orientation = xlPortrait
    .Draft = False
    <font color=red>.PaperSize = xlPaperLetter</font color=red>
    .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
    .Zoom = False
    <font color=red>.FitToPagesWide = 3
    .FitToPagesTall = False</font color=red>
    End With
    End Sub

    ... on the other hand, you may not be fix the problem at all.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Printing Problems (MS Excel 97/SR2a)

    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.
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •