Loading
Excel.Tips.Net ExcelTips (Menu Interface)

Conditional Printing

Please Note: This article is written for users of the following Microsoft Excel versions: 97, 2000, 2002, and 2003. If you are using a later version (Excel 2007 or later), this tip may not work for you. For a version of this tip written specifically for later versions of Excel, click here: Conditional Printing.

Kirk asked if there is a way to conditionally control what is printed in Excel. For instance, cell A1 contains a value, and the value controls exactly what is printed. Perhaps if A1 contains 1, then Sheet1 is printed; if it contains 2, then Sheet1 and Sheet2 are printed.

The only way to do this is with a macro, and there are several approaches you can use. Consider the following very simple macro, which simply uses a Select Case structure to control the printing.

Sub PrintStuff()
    Dim vShts As Variant

    vShts = Sheets(1).Range("A1")
    If Not IsNumeric(vShts) Then
        Exit Sub
    Else
        Select Case vShts
            Case 1
                Sheets("Sheet1").PrintOut
            Case 2
                Sheets("Sheet2").PrintOut
            Case 3
                Sheets("Sheet1").PrintOut
                Sheets("Sheet2").PrintOut
        End Select
    End If
End Sub

Run this macro with the value 1, 2, or 3 in cell A1 of the first sheet, and the macro prints different things based on the value. If the value is 1, then Sheet1 is printed; if it is 2, then Sheet2 is printed; and if it is 3, then both Sheet1 and Sheet2 are printed. If you want different values to print different things, just modify the Select Case structure to reflect the possible values and what should be printed for each value.

A more comprehensive approach can be created, as well. Consider adding a "control sheet" to your workbook. This sheet would have the name of each worksheet in the workbook listed in the first column. If you put a value to the right of a worksheet name, in the second column, then a macro will print the corresponding worksheet.

The following macro can be used to create the "control sheet."

Sub CreateControlSheet()
    Dim i as integer

    On Error Resume Next   'Delete this sheet if it already exists
    Sheets("Control Sheet").Delete
    On Error GoTo 0

    Sheets.Add   'Add the WhatToPrint Sheet
    ActiveSheet.Name = "Control Sheet"

    Range("A1").Select   'Label the columns
    ActiveCell.FormulaR1C1 = "Sheet Name"

    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Print?"

    Cells.Select
    Selection.Columns.AutoFit

    For i = 1 To ActiveWorkbook.Sheets.Count
        Cells(i + 1, 1).Value = Sheets(i).Name
    Next
End Sub

The macro first deletes any old control sheet, if it exists. It then adds a new worksheet named Control Sheet, and puts headers labels in columns A and B. It then lists all the worksheets in the workbook in column A.

With the control sheet created, you can then place an "X" or some other value (such as "Y" or 1) into column B beside each worksheet you want to print. The following macro then examines the control sheet and prints any worksheet that has a mark—any mark—in the cell in column B.

Sub PrintSelectedSheets()
    Dim i as Integer
    i = 2

    Do Until Sheets("Control Sheet").Cells(i, 1).Value = ""
        If Trim(Sheets("Control Sheet").Cells(i, 2).Value <> "") Then
            Sheets(Sheets("Control Sheet").Cells(i, 1).Value).Select
            ActiveWindow.SelectedSheets.PrintOut Copies:=1
        End If
        i = i + 1
    Loop
End Sub

Another approach is to create a macro that runs just before printing. (This is one of the events—printing—that Excel allows you to trap.) The following macro, added to the thisWorkbook object, is run every time you try to print or choose Print Preview.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Dim vShts As Variant
    Dim iResponse As Integer
    Dim bPreview As Boolean

    On Error GoTo ErrHandler

    vShts = Sheets(1).Range("A1")
    If Not IsNumeric(vShts) Then
        GoTo InValidEntry
    ElseIf vShts < 1 Or vShts > Sheets.Count Then
        GoTo InValidEntry
    Else
        iResponse = MsgBox(prompt:="Do you want Print Preview?", _
          Buttons:=vbYesNoCancel, Title:="Preview?")
        Select Case iResponse
            Case vbYes
                bPreview = True
            Case vbNo
                bPreview = False
            Case Else
               Msgbox "Canceled at User request"
               GoTo ExitHandler
        End Select

        Application.EnableEvents = False
        Sheets(vShts).PrintOut Preview:=bPreview
    End If

ExitHandler:
    Application.EnableEvents = True
    Cancel = True
    Exit Sub

InValidEntry:
    MsgBox "'" & Sheets(1).Name & "'!A1" _
        & vbCrLf & "must have a number between " _
        & "1 and " & Sheets.Count & vbCrLf
    GoTo ExitHandler

ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
End Sub

The macro checks the value in cell A1 of the first worksheet. It uses this value to determine which worksheets should be printed. In other words, a 1 prints the first worksheet, a 2 prints the second, a 3 prints the third, and so on.

If the value in A1 is not a value or if it is less than 1 or greater than the number of worksheets in the workbook, then the user is informed that the value is incorrect and the macro is exited.

Assuming the value in A1 is within range, the macro asks if you want to using Print Preview. Depending on the user's response, the macro prints the specified worksheet or displays Print Preview for that worksheet.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (2372) applies to Microsoft Excel 97, 2000, 2002, and 2003. You can find a version of this tip for the ribbon interface of Excel (Excel 2007 and later) here: Conditional Printing.

Related Tips:

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

 

Leave your own comment:

*Name:
Email:
  Notify me about new comments ONLY FOR THIS TIP
Notify me about new comments ANYWHERE ON THIS SITE
Hide my email address
*Text:
*What is 5+3 (To prevent automated submissions and spam.)
 
 
           Commenting Terms

Comments for this tip:

sridhar    02 Nov 2016, 02:28
i have 6 worksheet, 4th sheet was an invoice in which inoice no generated automatically while printing. After clubbing the new vba code whenever i print the other sheets the invoice no gets change.pls help me out.this is the code.

Private Sub CommandButton1_Click()
Dim sheetname As String
sheetname = Application.InputBox("Enter a SheetName")
Sheets(sheetname).PrintOut preview:=True
Dim num As Integer
Range("H1").Select
num = Range("H1").Value
num = num + 1
Range("H1").Value = num

End Sub
omer shafi    26 Oct 2016, 17:51
Is there a way to specify to use a particular printer when printing my spreadsheet, but use the default printer for everything else?.... I have 2 printers, but when I print this particular spreadsheet, I want to use a specific printer
omer shafi    26 Oct 2016, 17:21
I have a worksheet, and was wondering if you could guide me.

The workbook has 24 worksheet,

Main worksheet
label1
label2
label3 ..... & so on


I would like to enter a value in cell F4 on the main worksheet and it print the corresponding # of worksheets. If I enter 1, then only the first worksheet, if I enter 2, then worksheet 1&2, if I enter 3, then worksheet1,2,3

Thx for your guidnace


I was suing the following VBA per your website

Sub PrintStuff()
    Dim vShts As Variant

    vShts = Sheets(1).Range("F4")
    If Not IsNumeric(vShts) Then
        Exit Sub
    Else
        Select Case vShts
            Case 1
                Sheets("Label1").PrintOut
            Case 2
                Sheets("Label1").PrintOut
                Sheets("Label2").PrintOut
            Case 3
                Sheets("Sheet1").PrintOut
                Sheets("Sheet2").PrintOut
                Sheets("Label3").PrintOut
            Case 4
                Sheets("Label1").PrintOut
                Sheets("Label2").PrintOut
                Sheets("Label3").PrintOut
                Sheets("Label4").PrintOut
        End Select
    End If
End Sub
Olivia    17 Aug 2016, 13:23
My boss wants me to set a print rule for an order form I created. He wants to print only the items ordered and exclude unordered or zero quantity from being printed. Is there a rule for that?
Cam Cecil    27 Aug 2015, 07:54
Nice tip Allen.

Similar functionality can in fact be done without using macros.

In excel the print area is defined under Formulas -> Name Manager -> "Print_Area"

You can use regular excel functions to define the ranges, separating each conditional statement with comment.

For example, if I wanted to format a table to span for 2 pages, but only wanted to print page 2 if the first row of page 2 column B had a value:

=Sheet1!$A$1:$F$46,IF(Sheet1!$B$47<>"", Sheet1!$A$47:$F$95, "")
Anurag Gupta    03 Apr 2015, 01:55
Hey,

We have a excel template with details like prepared by, reviewed by, approved by alongwith other few values.

We want to prevent few non-continuous range of cells from printing (but they should be visible for editing in worksheet).

Can some help with non-macro bases conditional printing methodology or help me with macro based conditional printing alongwith example excel workbook.

Thanks in advance
Anurag Gupta
Goa
India
 
Ponsot    01 Apr 2015, 12:27
Hello,
Please, I try to prevent user from any printing of a certain Excel file.
I want them to fill in the Excel sheet instead of print+write on the piece of paper.

I tried with a password for example
Private Sub Workbook_beforePrint(Cancel As Boolean)
 
Secret = "noprint"
Question = InputBox("Mot de passe pour l'impression ?")
 
If Question <> Secret Then
 MsgBox ("No printing nor scribbling on paper pages. Please enter your hours and narratives on this file sheet 2")
 Cancel = True
End If
End Sub
But the print event is still running, not blocked.
Could you help me please, (and save the trees !)

Best regards
C.Ponsot
 
 

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us

 

Advertise with Us

Our Privacy Policy

Our Sites

Tips.Net

Beauty and Style

Cars

Cleaning

Cooking

DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)

Gardening

Health

Home Improvement

Money and Finances

Organizing

Pests and Bugs

Pets and Animals

WindowsTips (Microsoft Windows)

WordTips (Word 97–2003)

WordTips (Word 2007–2016)

Our Products

Helpful E-books

Newsletter Archives

 

Excel Products

Word Products

Our Authors

Author Index

Write for Tips.Net

Copyright © 2016 Sharon Parq Associates, Inc.