1

I recorded a macro to set my printer settings. I prompt the user as to whether or not they want to print the current report. Based on which report they ran, I send the Header variable to this printer setup code.

If I "run" this, the Report Header does not change from one report to the next. If I step through this code (f8) to a point 2 -3 lines past .CenterHeader = gblReportHeader and then press f5, the Report Header changes as I expect it to.

When I comment out the Application.PrintCommunication = line(s), the printpreview statement will flash the old report header and then display the proper report and header.

Any ideas why?

Sub PrintSettings(PrintZone)

' PrintSettings Macro               
Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .PrintTitleRows = ""
    .PrintTitleColumns = ""
End With
Application.PrintCommunication = True

ActiveSheet.PageSetup.PrintArea = PrintZone

Application.PrintCommunication = False
With ActiveSheet.PageSetup
    .LeftHeader = ""
    .CenterHeader = gblReportHeader
    .RightHeader = ""
    .LeftFooter = ""
    .CenterFooter = ""
    .RightFooter = ""
    .LeftMargin = Application.InchesToPoints(0.7)
    .RightMargin = Application.InchesToPoints(0.7)
    .TopMargin = Application.InchesToPoints(0.75)
    .BottomMargin = Application.InchesToPoints(0.75)
    .HeaderMargin = Application.InchesToPoints(0.3)
    .FooterMargin = Application.InchesToPoints(0.3)
'        .PrintHeadings = False
'        .PrintGridlines = False
'        .PrintComments = xlPrintNoComments
'        .CenterHorizontally = False
'        .CenterVertically = False
    .Orientation = xlPortrait
'        .Draft = False
    .PaperSize = xlPaperLegal
'        .FirstPageNumber = xlAutomatic
    .Order = xlDownThenOver
    .BlackAndWhite = False
'        .Zoom = False
    .FitToPagesWide = 1
    .FitToPagesTall = False
    .PrintErrors = xlPrintErrorsDisplayed
'        .OddAndEvenPagesHeaderFooter = False
'        .DifferentFirstPageHeaderFooter = False
    .ScaleWithDocHeaderFooter = True
    .AlignMarginsHeaderFooter = True
End With
Application.PrintCommunication = True

'    Application.Dialogs(xlDialogPrinterSetup).Show
ActiveSheet.PrintPreview

End Sub
DecimalTurn
  • 3,243
  • 3
  • 16
  • 36
Bimmie
  • 21
  • 1
  • 4
  • Did you try adding the `DoEvents` function before the PrintPreview statement? – DecimalTurn Feb 07 '19 at 23:18
  • Yes, I tried it right after .CenterHeader = , right before End With, right after End With, and right before ActiveSheet.PrintPreview. No change. – Bimmie Feb 08 '19 at 13:24
  • I added a for next loop with doevents in it. All that does is slow the program down. From what I read on doevents, it's not a good coding technique to use. – Bimmie Feb 08 '19 at 13:33
  • It's not ideal, but it can help diagnose the problem. Can you clarify what you mean by "correct report" vs "proper report"? These 2 sound like the same thing to me. – DecimalTurn Feb 08 '19 at 22:36
  • Ignore (correct report). I think maybe I started my sentence then, changed it. I would change my comment but don't seem to be able to edit my original post. Maybe you could expand on how you feel DoEvents can diagnose this issue. – Bimmie Feb 09 '19 at 01:35

1 Answers1

0

I have resolved my problem!

I commented out every line within the With ActiveSheet.PageSetup statement that I felt I did not need. This resolved my issue. I then started removing the commented lines back until I narrowed the issue to 5 lines.

.LeftHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""

I continued commenting these out one at a time and find that the .RightHeader = "" which is right after my .CenterHeader = gblReportHeader statement is the offending line of code.

I don't fully understand why but, being that these lines are not required, I have no qualms eliminating them so that my code works as expected.

Thank you to those who offered suggestions.

Bimmie
  • 21
  • 1
  • 4