SOLVED

How to prevent selected cells from printing, but have them visible?

Copper Contributor

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.

12 Replies

@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.

best response confirmed by BobsYourUncle54 (Copper Contributor)
Solution

@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 Sub

Insert 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 Sub

That should do it. I've attached a sample workbook with everything in place.

@BobsYourUncle54 

 

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 

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)

@Jan Karel Pieterse 

Thank you for your detailed, excellent instructions here.

I followed the directions, all goes well until I bring up the VBA editor. It is blank in there, no modules visible. The Option Explicit etc module is not there.

Perhaps I messed something up....

 

Thank you for your sample worksheet. It works as needed, perfect. Alt F11 brings up the modules as you have shown here.

 

I shut it all down and restarted to try again, it didn't save the new style. I redid it all but same results. I must be missing something. I followed your directions exactly.

 

If I can get this working, it will be what I need. It has to be kept simple when I give the sheet to sales guys. Open and use.

 

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.

@Jan Karel Pieterse 

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

@Jan Karel Pieterse 

You did the good work!

I just copied it! :)

@BobsYourUncle54 

 

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.

@BobsYourUncle54 

 

This feature is available in OpenOffice Calc and Writer

Dy34__0-1641545266373.png

 

I hope this feature will be available soon in MS Office

Great solution. Thank you.
1 best response

Accepted Solutions
best response confirmed by BobsYourUncle54 (Copper Contributor)
Solution

@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 Sub

Insert 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 Sub

That should do it. I've attached a sample workbook with everything in place.

View solution in original post