I had this request today at work for an Excel workbook and thought I would share it with you. Don’t get scared off, its a little Visual Basic (VB) for Excel- very simple though.
The request was to prevent users printing a certain Excel workbook-good idea. You may may not always want your workbooks to be printable especially if they contain some sensitive information that you would not want to be left around in the office or in someone else’s office!.
The easiest way to do this is to create a macro to cancel the print job before it even starts.
The details of the macro are below
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox “You can’t print this worksheet”, vbOKOnly, “Error”
If a user tries to print the workbook then the macros will automatcically cancel it and display a message that says “You can’t print this workbook” A bit like this below.
The addition of the message box is helpful as the controls for printing are still active in Excel and as the macro actually cancels the requested print job.
All you need to do to get this up and running is the following
- Hit ALT + F11
- Click on ThisWorkBook in the Project VBA Window
- You will now see the This Workbook code module- double click to open it
- Enter the above code into This Workbook code module
- Hit ATL+F11
- Save the the workbook
- Ta Da!
How To Excel At Excel Recommended Links
VBA Class is a structured and comprehensive online training program for learning Microsoft Excel VBA (Macros). It is full of real world examples & useful theory.
The aim of VBA Classes is to make a beginner an expert in VBA