Hiding Cells When Printing

BBrittain12

New Member
Joined
Apr 21, 2016
Messages
20
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?
 

Some videos you may like

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Damo71

Board Regular
Joined
Aug 17, 2010
Messages
88
Hi

In the workbook Module use

Code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)

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

Damo71

Board Regular
Joined
Aug 17, 2010
Messages
88
...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
 
Last edited:

BBrittain12

New Member
Joined
Apr 21, 2016
Messages
20
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

Board Regular
Joined
Aug 17, 2010
Messages
88
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

Board Regular
Joined
Aug 17, 2010
Messages
88
Ok, so I tested it and it works. therefore, by way of a reminder: in the WORKBOOK module, place the following:

Code:
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:

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,216
Messages
5,594,886
Members
413,947
Latest member
gizmolucy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top