Forum Discussion
Solved
How to prevent selected cells from printing, but have them visible?
I have created an extensive costing sheet for a sales rep that gets filled in and presented to a customer.
Part of one column contains dollar figures that need to be blank when printed, but must remain visible when entering data on the screen.
I have been searching, reading everything I can to figure out how to achieve this, but come up empty.
Is there any way to format a cell to be visible in the sheet, but not show up when printed?
It is not a whole column, so hiding it doesn't work.
I have tried setting the print area, selecting everything but the certain cells. That doesn't work either. It breaks my sheet into multiple pages to print in little sections.
Conditional formatting has no option for this.
Changing the cells to white font hides them from the user.
I tried setting cells only to hidden, but there is no option to not print hidden cells. I have looked everywhere for that.
I don't want to create macros, as this sheet gets emailed to people to fill in, and the antivirus software disables them. And besides, it just gets too complicated.
Something I thought so simple has become a headache.
Anyone know how to achieve this? - In a nutshell, certain cells visible but don't print.
BobsYourUncle54 Add a new cell style (see https://jkp-ads.com/articles/styles02.asp). Name the style HideWhenPrinting. Uncheck all boxes in the new style dialog, then click the Format button. On the Font tab, change the font to white and immediately change it back to Automatic. Click OK twice.
Highlight all cells which need to be absent from the print and set their style to the new HideWhenPrinting style.
Now hit alt+F11 to go to the VBA Editor. Double-click on the ThisWorkbook module in the project explorer, past this code:
Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) HideCells Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!ShowAgain" End SubInsert a new module (Insert, Module form the menu) and paste this code:
Option Explicit Sub HideCells() With ActiveWorkbook.Styles("HideWhenPrinting").Font .ThemeColor = 1 End With End Sub Public Sub ShowAgain() With ActiveWorkbook.Styles("HideWhenPrinting").Font .ColorIndex = xlAutomatic End With End SubThat should do it. I've attached a sample workbook with everything in place.
This feature is available in OpenOffice Calc and Writer
I hope this feature will be available soon in MS Office
- I totally agree with you.
I've been using OpenOffice for a long time and now I just purchased the Office 2021 Pro and I was very disappointed when I didn't find this option to hide when printing.
Another idea is by using filters. Have a column with an If/Then Statement with either 0 or 1 as the choices. In your application, it might be "=If(SellingPrice > 0, 1, 0)". So if no units are input for a product, the entry in this outer column remains 0, but it changes to 1 when the proposal shows any quantity of units.
Then apply a filter on that column. When you uncheck 0 as an option, it will hide those rows. Make sure to set your print range to not include the outer column.Sometimes, you can hard code a 0 into a cell when the If/Then isn't quite applicable.
To the others who replied, thank you for your thoughts and input.
I do have to keep it user simple, they won't be changing anything or formatting themselves. It all has to be there in the sheet. Enter, Print, Done.
Here is an out-of-the-box solution you can try and see if it works for you.
Make an image file that is just a large simple gray box/rectangle
Go to page layout select background and choose that gray box file
Now you want to have all the data you want inside that gray box and now you can have your $$ cells formatted in white text and still see them but then when you print, you make sure it doesn't print the background (which when I tested this solution I actually couldn't find how TO print the background)
I attached my "gray box" file (1 less thing you need to do)
Since you seem familiar with conditional formatting, let me suggest that you have a cell "off camera" where you can enter something along the lines of "Print" and then use conditional formatting in the subject cell so that it whites out the figure when that cell contains "Print"
My contingency planning mindset, though, says I need to ask you "How serious would it be if somebody inadvertently prints with these figures visible and shows it to the customer?"
I ask because even if the probability is low, if it would be very serious, you might want to consider a more radical preventive action. We all get forgetful at times....over time...and I for one would consider it quite likely that the day will come when somebody innocently forgets to hide that cell. At that point--again, if it's serious--you'll wish you had done something else; like creating one layout clearly marked for internal use only, and another for customers. Use the same data--don't get redundant on that--but have two different output documents.
BobsYourUncle54 Add a new cell style (see https://jkp-ads.com/articles/styles02.asp). Name the style HideWhenPrinting. Uncheck all boxes in the new style dialog, then click the Format button. On the Font tab, change the font to white and immediately change it back to Automatic. Click OK twice.
Highlight all cells which need to be absent from the print and set their style to the new HideWhenPrinting style.
Now hit alt+F11 to go to the VBA Editor. Double-click on the ThisWorkbook module in the project explorer, past this code:
Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) HideCells Application.OnTime Now, "'" & ThisWorkbook.FullName & "'!ShowAgain" End SubInsert a new module (Insert, Module form the menu) and paste this code:
Option Explicit Sub HideCells() With ActiveWorkbook.Styles("HideWhenPrinting").Font .ThemeColor = 1 End With End Sub Public Sub ShowAgain() With ActiveWorkbook.Styles("HideWhenPrinting").Font .ColorIndex = xlAutomatic End With End SubThat should do it. I've attached a sample workbook with everything in place.
Will this work if I'm trying to hind columns that are needed internally for information in the spreadsheet but not when printed to be shown to a 3rd party? I tried the suggestion and unfortunately, it's not working for what I need.
- Great solution. Thank you.
I just went back and tried again. I added the first module you showed, then the second one.
It worked perfectly! Awesome, thank you for your help, I really appreciate that.
I can send the sheet to the sales guys now and have it not print our internal pricing structure
- Good work!
BobsYourUncle54 What come to mind is that you re-design your schedule so that the cells that should not be printed (but visible upon input) are outside the print area.
