Apr 29 2020 08:28 AM
Apr 29 2020 08:28 AM
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.
Apr 29 2020 08:56 AM - edited Apr 29 2020 09:07 AM
@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.
Apr 29 2020 08:58 AMSolution
@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.
Apr 29 2020 09:04 AM
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.
Apr 29 2020 09:27 AM - edited Apr 29 2020 09:31 AM
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)
Apr 29 2020 10:44 AM
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.
Apr 29 2020 10:47 AM
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.
Apr 29 2020 11:02 AM
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
Apr 29 2020 11:24 AM
You did the good work!
I just copied it!
Mar 05 2021 11:36 AM - edited Mar 05 2021 11:38 AM
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.