×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Printer disable

Printer disable

Printer disable

(OP)
I would like to disable the ability to print certain worksheets in excel. The purpose is to force the user to print worksheets using an on screen button which triggers VBA code.

Is it possible to write an event procedure which would stop the printing process whenever the normal print dialogue was used for printing? If so would it also stop a VBA initiated print sequence?

I am also thinking that an event procedure for workbook activation could set the default printer to null or have all printouts go to a temporary print file. The result being that it would appear that the file had not printed thus requiring the user to click the button I want them to click. The chosen VBA code would simply choose the appropriate printer for a successful print out.

The complication I see for changing the default printer is that it would persist into other workbooks and applications.

Any thoughts or suggestions would be appreciated.

RE: Printer disable

You could use the Workbook BeforePrint event handler that includes a Cancel paramter.  Setting this to false terminates the print operation.  Test a global boolean variable inside this event handler, which your code sets to true before the VBA initiated print.  Example:

CODE

Private Sub Workbook_BeforePrint(Cancel As Boolean)
   If Not AllowPrint Then Cancel = True
End Sub


Regards,
Mike

RE: Printer disable

(OP)
Thanks, that solution is more effective than I was hoping for.

Where do I place the code? I am thinking in the "Thisworkbook" part of the VBAproject.

I am not sure where to define the Allowprint variable. I have not very familiar with global variables. The variables I have used are limited to either within a private subroutine or within a module containing multiple private subs. This variable would have to be used in multiple modules in one workbook.

If I can't make the variable work on multiple modules I can put all the code into one module. What are my options?

RE: Printer disable

The Workbook_BeforePrint event handler does, indeed, reside in the ThisWorkbook class module.

Standard code modules (which you invoke through Insert|Module in the VBE) are global within their VBAProject and, in fact, are available to other workbooks/VBAProjects (unless an Option Private Module directive is placed at the top).  Therefore, you can place your boolean variable in any standard code module, but remember to declare it using Public rather than Dim.

The other option is if your print procedure is located in ThisWorkbook, you could declare AllowPrint in that module, but using Dim instead of Public, as public (global) variables are not allowed .


HTH
Mike

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources