• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Print Setup in VBA

glennpc

Member
Question on using VBA to print. I have a data table, and I want to generate reports from it. There will be a worksheet with buttons to run the various reports. Behind those buttons will be macros that filter the table, sort the results, and send the user to the print preview where they can decide to go ahead with the print or cancel out. Regardless of what they choose, the macro will (after the print or the cancel) return them to the worksheet with the table and will undo the sorting and filtering so it is in its normal state.


Question: I have code to set up the printed sheets with headers and footers and the reports will have a different title in the header. The code is:

[pre]
Code:
With ActiveSheet.PageSetup
.LeftHeader = "MAPD LESSONS LEARNED"
.CenterHeader = "MASTER TABLE (All Rows), Sorted by Item Number"
.RightHeader = "Printed on:  &D"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = "Page &P of &N"
.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
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperLetter
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = False
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
[/pre]

The question is: do I need ALL of this in each macro? I'm thinking if I don't, and only include, say, the lines that set up the header and footer text, if the user runs some other print operation in between running my macros, it could UN-DO what this code does for some of the other things (like for example page orientation). Then when they run my macros again, they'll get a portrait orientation when they are expecting landscape.


Any words of wisdom on this?
 
Hi ,


Logically speaking , each macro needs to include only as many options as need to be configured for that report ; if certain options are being set to the same selection in all the macros , then it is better you set them manually once , and then remove the code for setting them from all the macros , but if one report page orientation is Portrait while another's is Landscape you have two options ; either set this option in each macro , or if most of the reports are in Portrait mode , while only a few are in Landscape mode , then after the printing is done in Landscape mode , put it back to Portrait mode within the same macro , so that the majority which use Portrait mode will not need to set it to that.


You can have a subroutine to put the relevant options to the default values , so that you don't need to repeat the code in every macro.


Narayan
 
In addition to what Narayan said above, you need to remember that a lot of dialogs have default values. These may be changed by a user or by default printers as in this case.


You should set all fields to values that you want early in the macro

then only change the fields need to be changed for each loop/iteration/page etc
 
Back
Top