Hiding Cells When Printing

[BBrittain12]

Hello,

I have a questions regarding hiding cells when the File: Print is selected. What I would like is some type of VBA to run in the background that hides specific columns when File Print is prompted.

So I have a workbook that has multiple sheets which information in column D, E and F that a user see's, but when he prints the information I want these columns to hide.

I have a macro with a button that does it now, but sometimes the user forgets to push the button to hide the cells, so i want to try a different tactic.

Anyone have any ideas?

 

[Damo71]

Hi

In the workbook Module use

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Place your code there but remember to specify which sheet you are wanting to alter.

 

[Damo71]

...or, instead of having the user click a button to hide the cells before he then prints..

Create code that hides the data AND prints the document, and ask the user to click that button to do his printing, instead of using the normal File>Print commands in Excel

 

[BBrittain12]

What code? And the issue is each sheet will be a different WO# so i will never know the sheet name...

That how i have the code wrote now, but we aren't working with the brightest individuals so that's why I'm having to try this approach

 

[Damo71]

Hi. I’m not at a pc, so I can’t test this.
But try it.
Press Alt + F11 and paste the following workbook In the Workbook module,
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "You can't print this workbook using the normal print command, please use the button on the sheet"
End Sub

Then modify your own existing code so that it starts with “application.enableevents=false”, then hide the columns, print the sheet, then unhide the columns.
Then before the EndSub line add ”application.enableevents=true”

The idea is that the first piece of code will prevent the user from using the normal print route, but you need to add a print line to your existing code so that the user is forced to print from your own buttons. The only thing I’m not sure of is whether the disabling application events will work on the “before print” workbook code so you need to test it

 

[Damo71]

Ok, so I tested it and it works. therefore, by way of a reminder: in the WORKBOOK module, place the following:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
    Cancel = True
    MsgBox "You can't print this workbook using the normal Print command, please use the button on the sheet"
End Sub

And, (I'm basing this only on the fact that the only things your printout needs to hide are columns D to F), in a normal module, place this code:

Sub HideDEFandPrint()
   'Prevent Worksheet code from stopping your print and prevent screen flicker
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
   'Hide and print your columns
    Columns("D:F").EntireColumn.Hidden = True
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True, _
        IgnorePrintAreas:=False
        
   'Unhide your columns
    Columns("D:F").EntireColumn.Hidden = False
    
   're-enable the prevent print code to stop manual printing
    Application.EnableEvents = True
   
End Sub

The active sheet is the one that will be printed, so no need to mention sheet name.