Got a version of Excel that uses the menu interface (Excel 97, Excel 2000, Excel 2002, or Excel 2003)? This site is for you! If you use a later version of Excel, visit our ExcelTips site focusing on the ribbon interface.
With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company.
Learn more about Allen...
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.
Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!