Solved

VBA Print Code - Print Area & Fit on one page

Posted on 2013-06-13
4
10,781 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

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 …
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

810 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