Excel:Print specific pg based on cell content

Tags:
Microsoft Excel 2003 (full product)
excel
print
macro
September 7, 2010 at 11:22:17
Specs: Windows XP
Hello,

I have a workbook in excel with a number of different worksheets. I would like a way to set it so that when a user prints the workbook it only prints specific pages based on whether a specific cell in each worksheet has content or not.

Here are the names of each of the worksheets along with the cell that is of importance:

Main - I never want this to print
New AX43
Cont_Sheet B3
Orange AX43
O2 AX43
Vodafone AX43
T-Mobile AX43
Three AX43

So, for example, if the cell AX43 on the worksheet named "Orange" has something in it, then when the user presses print, I want worksheet "Orange" to print. Same for all the other worksheets where the specified cell above is full. Any worksheets where the specific cell is empty, then I want that worksheet omitted when printing the workbook.

Is this possible?

Many thanks in advance

Amy.


See More: Excel:Print specific pg based on cell content

Report •

#1
September 7, 2010 at 13:01:42
Try this:

Press Alt-F11 to open the VBA editor.

Double Click the ThisWorkbook module in the left hand column.

Paste this code in the pane that opens. It will run automatically each time the user prints the workbook

Read the comments to see what it does.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Disable events while code runs
 Application.EnableEvents = False
'Loop through sheets
  For shts = 1 To Sheets.Count
'Read Sheet Name
    Select Case Sheets(shts).Name
'If Name = Main, Don't Print it
      Case "Main"
        Cancel = True
'If Name = Cont_Sheet, Check B3, print if not empty
      Case "Cont_Sheet"
        If Sheets(shts).Range("B3") <> "" Then _
            Sheets(shts).PrintOut
'All other sheets, check AX43, print if not empty
      Case Else
        If Sheets(shts).Range("AX43") <> "" Then _
            Sheets(shts).PrintOut
    End Select
  Next
 Application.EnableEvents = True
End Sub


Report •

#2
September 7, 2010 at 13:51:31
Oh fantastic! Thanks so much for the quick reply.

Unfortunately I don't have a printer to test it on but I've added the code (looks perfect to me) and I've sent it to someone to check for me.

I bow down to you.. :)

Thank you x


Report •

#3
September 8, 2010 at 01:17:46
Hi,

I'm being told that when they press print, nothing happens at all.

If the dependent cell has a rule in it would that make a difference? For example, AX43 on the Vodafone tab is =IF(G6="","", sum(A1:B5)*2) - so would the print rule suggest that that cell is full even though it might look empty to the eye? Not that that would be the cause of this issue, it was just something else I thought about.

Any ideas?


Report •

Related Solutions
See More ↓

#4
September 8, 2010 at 10:53:20
re: "I'm being told that when they press print, nothing happens at all.

I just want to say that if testing this code is going to have to go through a third person, this might be tough. Since you can only report what you are "being told" we are adding a level of uncertainly here.

That said, the code works fine for me. I set up a worksheet with 5 sheets, using names from your example. I made sure I had a sheet named Main and Cont_Sheet because those 2 sheets are handled differently than the rest - Main is never printed and Cont_Sheet is printed based on B3, not AX43.

I then put your formula in AX43, entered values in A1:B5, and either put something G6 or didn't. When there was value in G6 on a given sheet, a value appeared in AX43 and the sheet printed.

If there was a value in Cont_Sheet!B3, the sheet printed. If there wasn't, it didn't print.

Just to prove to you that the logic works, I modified the code so that you can test it without a printer. As written below, it will follow the same rules as the previous code I offered, but instead of printing, it will pop up a message box telling you which sheets would have been printed if you had a printer.

Granted, it's not the same code as you sent to your "testers" but the logic is exactly the same.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
'Disable events while code runs
 Application.EnableEvents = False
'Loop through sheets
  For shts = 1 To Sheets.Count
'Read Sheet Name
   Select Case Sheets(shts).Name
'If Name = Main, Don't Print it
     Case "Main"
     Cancel = True
       MsgBox "I'll Never Print The Main Sheet"
'If Name = Cont_Sheet, Check B3, print if not empty
     Case "Cont_Sheet"
       If Sheets(shts).Range("B3") <> "" Then
        Cancel = True
        MsgBox "Cont_Sheet Would Have Been Printed If You Had A Printer"
            End If
'All other sheets, check AX43, print if not empty
     Case Else
       If Sheets(shts).Range("AX43") <> "" Then
        Cancel = True
        MsgBox Sheets(shts).Name & " Would Have Been Printed If You Had A Printer"
            End If
    End Select
  Next
 Application.EnableEvents = True
End Sub


Report •

#5
September 10, 2010 at 10:23:45
Woohooo! Dad you're amazing.

I loved the little messages. And I've just managed to test with a printer and it's working.

That'll teach me to ask someone to test it for me.

Many many many thanks.

Ames x


Report •
Start a discussion

Ask Question