Print button with preferences on each sheet

[RKB]

Dear forum members,

I have made a catalogue with multiple sheets in excel.

I want to add a print button on each page with pre set Page Setup (tab Page) for printing.

Options that i want to preset are (excel 2003):

1. Orientation: Portrait or Landscape

2. Scaling: Adjust to X% normal size and/or Fit to X page(s) wide by Y tall

After hitting the button the printing should start right away without bringing up the print window first.

Hopefully someone here can help me....

bye, Rudi


Resources I found so far:
http://www.mrexcel.com/archive/Printing/2306.html
http://www.computing.net/answers/office/excel-create-print-button/7734.html

 

[Barry Katcher]

Try the code below, altered to suit for each of your sheets. Create a button on each page and assign the appropriate macro to each.

Sub PrintPage1()

    Range("A1:E7").Select '(change print range to suit)
    ActiveSheet.PageSetup.PrintArea = "$A$1:$E$7" '(change print range to suit)
    With ActiveSheet.PageSetup
        .Zoom = 150 '(change or eliminate zoom to suit)
        .Orientation = xlLandscape '(change to xlPortrait to suit)
        .FitToPagesWide = 2 '(change or eliminate to suit)
        .FitToPagesTall = 2 '(change or eliminate to suit)
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Range("A1").Select
End Sub

 

[RKB]

Hi Barry,

Thanks for your help.

I have inserted an autoshape and assigned your macro to it.

But when I click the autoshape it starts printing 6 pages (same as without page setup) while i have a different setup. The code I use is as follows:

Sub PrintPage1()
Range("A1:G57").Select '(change print range to suit)
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$57" '(change print range to suit)
With ActiveSheet.PageSetup
.Zoom = 100 '(change or eliminate zoom to suit)
.Orientation = xlPortrait '(change to xlPortrait to suit)
.FitToPagesWide = 1 '(change or eliminate to suit)
.FitToPagesTall = 2 '(change or eliminate to suit)
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1").Select
End Sub


Also, the area i want to print is from A1 to A57 to G57 to G1 ( a square). Did i fill in the two fields of the macro right?

Bye

 

[Barry Katcher]

Your line
.Zoom = 100 '(change or eliminate zoom to suit)
is in conflict with your
.FitToPagesWide = 1 '(change or eliminate to suit)
.FitToPagesTall = 2 '(change or eliminate to suit)

You can't have both - when, in Page Setup, you click on "Fit to", it unchecks "Adjust to", and vice versa. That's why I followed each with the comment (change or eliminate .... to suit).

You have to decide which of the two sets you want to use. If you want to fit to x wide by x tall, then place an apostrophe in front of
.Zoom = 100 '(change or eliminate zoom to suit).

This will turn the zoom part of the macro into a comment and it won't be executed. Or you can just delete that line.

Conversely, if you want to zoom, then place the apostrophe in front of the two FitTo.. lines, or delete them.

Another thing to note. If you choose zoom mode, as opposed to Fit to, the number of pages that print will be dependent on how wide your columns are and how tall your rows are. The wider the columns, the more pages will be used to print the full width of the print range (A:G). And the taller the rows (for instance, if you wrap text), the more pages will be needed to print the full height of the range (1:57).

 

[lenze]

Totally untested, but might be an easy way

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet
Select Case .Name
    Case "Sheet1" 'Settings for Sheet1
       With PageSetUp
           .LeftFooter = "xxxx"
           .Zoom = 100
           'etc
       End With
    Case "Sheet2" 'Settings for Sheet2
       With PageSetUp
           .Zoom = 75
            'etc
       End With
     'etc
     Case Else:
End Select
End With
End Sub

lenze

 

[RKB]

Barry,

I did exactly what you said: eliminate the 'zoom line' by deleting it and also by using a '.

But it still prints as if there is no custom setup....

Code looks like this:

Sub PrintPage1()
Range("A1:G57").Select '(change print range to suit)
ActiveSheet.PageSetup.PrintArea = "$A$1:$G$57" '(change print range to suit)
With ActiveSheet.PageSetup
.Orientation = xlPortrait '(change to xlPortrait to suit)
.FitToPagesWide = 1 '(change or eliminate to suit)
.FitToPagesTall = 2 '(change or eliminate to suit)
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Range("A1").Select
End Sub


Any idea of what can be wrong here?

 

[Barry Katcher]

Try this - I added a .Zoom = False before the FitTo lines. Apparantly, this was needed to eliminate the prior zoom condition of the page.

I also added a first line of Sheets("Sheet1").Select to insure that you're on the right tab. Make sure that the page name within the quotes matches the name of the tab.

Sub PrintPage1()
    Sheets("Sheet1").Select
    Range("A1:G57").Select
    ActiveSheet.PageSetup.PrintArea = "$A$1:$G$57"
    With ActiveSheet.PageSetup
        .Orientation = xlPortrait
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 2
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Range("A1").Select
End Sub

 

[lenze]

This, in the ThisWorkBook module, works

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Sheet1"
    With ActiveSheet.PageSetup
        .RightFooter = Format(Date, "mmm-dd-yyyy")
        .CenterFooter = "This is Sheet1"
        .LeftFooter = "Printed by " & Environ("UserName")
        .Orientation = xlLandscape
        .Zoom = 85
    End With
Case "Sheet2"
    With ActiveSheet.PageSetup
        .RightFooter = Format(Date, "mmm-dd-yyyy")
        .CenterFooter = "This is Sheet2"
        .LeftFooter = "Printed by " & Environ("UserName")
        .Orientation = xlPortrait
        .Zoom = 100
    End With
End Select
End Sub

All you need to do is select Print from the tool bar. No button is needed. Just requires a little upfront work to set it up for each sheet.
lenze

 

[RKB]

Barry,

It works flawlessly now. thanks!


@lenze: i have chosen for the other code as it has the option for page fitting. Also, it is used for a catalogue and I want my customers to have the choice whether printing through the button with pre setup or their own setup. thanks anyway!