Printer disable

[bpeirson]

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.

 

[msmith]

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:

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

Regards,
Mike

 

[bpeirson]

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?

 

[msmith]

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