Forum Discussion

BobsYourUncle54's avatar
BobsYourUncle54
Copper Contributor
Apr 29, 2020

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.

excel
Formulas and Functions
  • JKPieterse's avatar
    JKPieterse
    Apr 29, 2020

    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.

  • JKPieterse's avatar
    JKPieterse
    Silver Contributor
    Apr 29, 2020

    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's avatar
      BobsYourUncle54
      Copper Contributor
      Apr 29, 2020

      JKPieterse 

      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.

       

    • BobsYourUncle54's avatar
      BobsYourUncle54
      Copper Contributor
      Apr 29, 2020

      JKPieterse 

      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

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Apr 29, 2020

    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.

  • mathetes's avatar
    mathetes
    Silver Contributor
    Apr 29, 2020

    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.

  • Dy34_'s avatar
    Dy34_
    Copper Contributor
    Jan 07, 2022

    BobsYourUncle54 

     

    This feature is available in OpenOffice Calc and Writer

     

    I hope this feature will be available soon in MS Office

    • ReggieLucas's avatar
      ReggieLucas
      Copper Contributor
      Jul 27, 2024
      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.
  • mtarler's avatar
    mtarler
    Silver Contributor
    Apr 29, 2020

    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)

  • BobsYourUncle54's avatar
    BobsYourUncle54
    Copper Contributor
    Apr 29, 2020

    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.

  • CDJay's avatar
    CDJay
    Copper Contributor
    Mar 05, 2021

    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.