Print button with preferences on each sheet

RKB · Apr 29, 2009

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 · Apr 29, 2009

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.

Code:

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 · Apr 30, 2009

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 · Apr 30, 2009

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 · Apr 30, 2009

Totally untested, but might be an easy way

Code:

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 · May 1, 2009

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 · May 1, 2009

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.

Code:

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 · May 1, 2009

This, in the ThisWorkBook module, works

Code:

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 · May 2, 2009

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!

Print button with preferences on each sheet

RKB

New Member

Excel Facts

Barry Katcher

Well-known Member

RKB

New Member

Barry Katcher

Well-known Member

lenze

Legend

RKB

New Member

Barry Katcher

Well-known Member

lenze

Legend

RKB

New Member

Similar threads

Forum statistics

Share this page

Print button with preferences on each sheet

New Member

Excel Facts

Well-known Member

New Member

Well-known Member

Legend

New Member

Well-known Member

Legend

New Member

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock