Still stumped with printing macro

[zenahs]

I'll be the first to admit that I'm a rookie at this whole VBA thing.
All of the code that I'm currently using in my workbook is totally
accredited to information I've found through searching and posting in
this forum. I have virtually no comprehension of the syntax or logic
involved in writing my own code. So, please feel free to answer this
question in the most elementary way possible.
I've successfully employed code to prevent users of my workbook from
printing. The following code was written to "This Workbook":
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Information contained in this workbook cannot be printed"
End Sub
Here is my problem...I cannot seem to find a way to trigger this event
with a button instead of triggering upon opening the workbook. There
are some instances where a few users (with password privileges) may
have to print a page from the workbook before forwarding it on to the
ultimate end user who cannot print anything. If there was some way to
toggle this print prevention with a button it would be perfect!
Can anyone help??

 

[Ron de Bruin]

Hi zenahs

You can create a button that change the EnableEvents

Sub test()
Application.EnableEvents = False
ActiveSheet.PrintOut
Application.EnableEvents = True
End Sub

 

[zenahs]

Thanks Ron.
I've copied this macro into the workbook and attached it to a button.
Unfortunately, when I press the button the active sheet prints. Am
doing something wrong?
Is there a way to deactivate printing for the entire workbook instea
of just the active sheet

 

[Ron de Bruin]

I think you have EnableEvents set to False
Or you have disable macro's when you open the workbook

Run this one time
Application.EnableEvents = True
Save and close/reopen the file

Now you can't use the Print button if you have your code in the Workbook_BeforePrint
in the thisworkbook module

With the code I posted you can prin the activesheet now

 

[Guest]

This should do what you want:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
dim sPassword as String

sPassword = InputBox("Enter the password")
if Not sPassword = "WhatEverThePasswordIs" Then
Cancel = True
MsgBox "Information contained in this workbook cannot be printed"
Else
ActiveSheet.PrintOut
End If
End Sub

Regards,
GS