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
        Select Case vShts
            Case 1
            Case 2
            Case 3
        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"

    ActiveCell.FormulaR1C1 = "Print?"


    For i = 1 To ActiveWorkbook.Sheets.Count
        Cells(i + 1, 1).Value = Sheets(i).Name
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
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
        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

    Application.EnableEvents = True
    Cancel = True
    Exit Sub

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

    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:

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!


Leave your own comment:

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

Comments for this tip:

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

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
Ponsot    01 Apr 2015, 12:27
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

Our Company

Sharon Parq Associates, Inc.

About Tips.Net

Contact Us


Advertise with Us

Our Privacy Policy

Our Sites


Beauty and Style




DriveTips (Google Drive)

ExcelTips (Excel 97–2003)

ExcelTips (Excel 2007–2016)



Home Improvement

Money and Finances


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.