Solved

VBA Print Code - Print Area & Fit on one page

Posted on 2013-06-13
4
12,470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
This article describes how you can use Custom Document Properties to store settings and other information in your workbook so that they will be available the next time you open the workbook.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

687 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