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.
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 SubHi,
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?
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