Solved

VBA Print Code - Print Area & Fit on one page

Posted on 2013-06-13
4
11,280 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Can't get rid of Update Links message in Excel 2013 8 32
batch file or script 4 27
Excel 2007 VB Code for GCF 7 18
Help with Excel 17 29
User Beware!  This is a rather permanent solution to removing your email from an exchange server.  The only way to truly go back is to have your exchange administrator restore your mailbox from backups.  This is usually the option of last resort.  A…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

830 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