Forum Discussion
How to prevent selected cells from printing, but have them visible?
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.
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.
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!
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.
