• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 14862
  • Last Modified:

VBA Print Code - Print Area & Fit on one page

Hello Experts,

I am currently using this code for printing.

Option Explicit

Sub Pallet_Tag_PrintSelector()
    Dim PrintSelection As Integer
    Dim AllowButtonActions As Boolean
    Dim DisplayMsg As String

    ' Get TRUE or FALSE value from Defined Name "AllowButtonActions"
    AllowButtonActions = Range("AllowButtonActions").Value
    
    ' Test the AllowButtonActions value to determine if the Proc should continue
    If Not AllowButtonActions Then
        DisplayMsg = MsgBox("Printing has been disabled, until you fill in all required cells.", vbOKOnly, "Printing Disabled")
        Exit Sub
    End If

    PrintSelection = Range("PrintSelection").Value
    If PrintSelection = 0 Then
        Call Pallet_Tag_PrintAll
    Else
        'MsgBox PrintSelection
        ThisWorkbook.Worksheets("Preview").PrintOut
    End If
        Range("PrintSelection").Value = ""
End Sub
Sub Pallet_Tag_PrintAll()
    Dim i As Integer
    Dim palletCount As Integer
    Dim PrintSelection As Integer
        
    palletCount = Range("TotalPallets").Value
    
    For i = 1 To palletCount
        Range("PrintSelection").Value = i
        'MsgBox PrintSelection
        ThisWorkbook.Worksheets("Preview").PrintOut
    Next 'i

        Range("PrintSelection").Value = ""

End Sub

Open in new window


A problem I have noticed, is that it's not always printing the correct range.  I have setup the print area (not via code) - and set it so that it always prints to 1 page (not via code).

But I noticed printing this on other workstations - sometimes it print correctly, and other times it didn't.

I am now thinking, the PRINT AREA & PRINT TO ONE PAGE - needs to be coded into VBA, regardless of what the print settings are of the workstation.

Page to be printed = Preview
Print area references cell range = B2:G26
B2:G26 is NAMED = Print_Area

I am hoping someone can add to my VBA code, and force the print to ONLY print that specific print area, and make it always fit onto 1 page only - regardless of what settings are on the users computer.  I need to make sure it prints the same ALL the time, regardless of what workstation it's on.

Thank you in advance for your help!

~ Geekamo
0
Geekamo
Asked:
Geekamo
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
Add the following:

    With Worksheets("name").PageSetup
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With

Open in new window

0
 
GeekamoAuthor Commented:
@ matthewspatrick,

Do I put your line of code, right above line: ThisWorkbook.Worksheets("Preview").PrintOut ?

Is name the name of my worksheet?

I notice there's nothing mentioning the Print Area. Is the zoom taking care of that?

Sorry for the dumb questions, I get lost easily with this. :-)

~ Geekamo
0
 
Patrick MatthewsCommented:
Sorry, forgot the print area:

    With ThisWorkbook.Worksheets("Preview").PageSetup
        .PrintArea = "b2:g26"
        .Zoom = False
        .FitToPagesTall = 1
        .FitToPagesWide = 1
    End With

Open in new window


And yes, that would go before your PintOut command.
0
 
GeekamoAuthor Commented:
@ matthewspatrick,

Thank you very much for your help!  I appreciate it.  After testing the workbook on several workstations at the office, it printed correctly 100% of the time.

~ Geekamo
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now