Solved

VBA Print Code - Print Area & Fit on one page

Posted on 2013-06-13
4
10,240 Views
Last Modified: 2013-07-12
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
Comment
Question by:Geekamo
  • 2
  • 2
4 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39246220
Add the following:

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

Open in new window

0
 
LVL 1

Author Comment

by:Geekamo
ID: 39246299
@ 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
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 500 total points
ID: 39246437
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
 
LVL 1

Author Comment

by:Geekamo
ID: 39250841
@ 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We were having a lot of "Heartbeat Alerts" in our SCOM environment, now "Heartbeat" in a SCOM environment for those of you who might not be familiar with SCOM is a packet of data sent from the agent to the management server on a regular basis, basic…
This collection of functions covers all the normal rounding methods of just about any numeric value.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now