Print size change using VBA.

[imback2nite]

I'm hoping this is a stupid question. I use the code below to print a page. The problem is when I print to pdf, it comes out fine. Even when I print the pdf file. When I print to paper the print range I set is not used. The page prints twice the size in width. What am I doing wrong? Thank you in advance.

Private Sub CommandButton4_Click()
    Application.ScreenUpdating = False
   YesNo = MsgBox("This Action Will Print, 'Tally Sheet'      " & vbCr _
    & "                   Continue?", vbYesNo + vbInformation, "Caution")
    Select Case YesNo
    Case vbYes
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$19"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$B$1:$N$319"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Tahoma,Bold""CONFIDENTIAL"
        .RightHeader = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0#)
        .RightMargin = Application.InchesToPoints(0#)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .Zoom = 52
        .Draft = False
    End With
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Range("B1").Select
     Application.ScreenUpdating = True
Case vbNo
    Application.ScreenUpdating = False
    Range("B1").Select
    Application.ScreenUpdating = True
    End Select
End Sub

 

[Skyybot]

Try this (small edit to your code).

Private Sub CommandButton4_Click()
    Application.ScreenUpdating = False
   YesNo = MsgBox("This Action Will Print, 'Tally Sheet'      " & vbCr _
    & "                   Continue?", vbYesNo + vbInformation, "Caution")
    Select Case YesNo
    Case vbYes
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$19"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$B$1:$N$319"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Tahoma,Bold""CONFIDENTIAL"
        .RightHeader = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0#)
        .RightMargin = Application.InchesToPoints(0#)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .Zoom = 52
        .Draft = False
    End With
    ActiveSheet.PrintOut Copies:=1, Collate:=True
    Range("B1").Select
     Application.ScreenUpdating = True
Case vbNo
    Application.ScreenUpdating = False
    Range("B1").Select
    Application.ScreenUpdating = True
    End Select
End Sub

 

[imback2nite]

This is what is happening. The first image is what it looks like printing to the printer. The second image is printing to PDF. The third is printing to PDF and THEN printing to the printer. I tried the change but still not working.

 

[Skyybot]

If this doesn't work the only thing that I can think of is setting Preview to True so that you can make the necessary changes before printing.

Private Sub CommandButton4_Click()
    Application.ScreenUpdating = False
   YesNo = MsgBox("This Action Will Print, 'Tally Sheet'      " & vbCr _
    & "                   Continue?", vbYesNo + vbInformation, "Caution")
    Select Case YesNo
    Case vbYes
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$19"
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = "$B$1:$N$319"
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&""Tahoma,Bold""CONFIDENTIAL"
        .RightHeader = ""
        .CenterFooter = "Page &P of &N"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0#)
        .RightMargin = Application.InchesToPoints(0#)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.25)
        .FooterMargin = Application.InchesToPoints(0.25)
        .CenterHorizontally = True
        .Orientation = xlPortrait
        .Zoom = 52
        .Draft = False
    End With
    ActiveSheet.PrintOut Copies:=1, Collate:=True, IgnorePrintAreas:= True
    Range("B1").Select
     Application.ScreenUpdating = True
Case vbNo
    Application.ScreenUpdating = False
    Range("B1").Select
    Application.ScreenUpdating = True
    End Select
End Sub

 

[imback2nite]

No. Still no dice. I had to resort to
.Zoom = 51
The print was already small. What's so odd is I'm using the same print code. What's the difference between paper and PDF?