Macro Created but Not Running in Workbook

[alexaed]

Hello All,
I'm very new to this excel macro writing and have been able to create
and run a macro to prohibit printing.

It runs in the VB editor, but not in the actual workbook. I can select
File > Print and the window for printing comes right up. How do I
apply this macro to the workbook effectively?

(I feel stupid for asking because its probably simple, but I need
help).

Thanks,
Ed

 

[Gary''s Student]

Put the following in Workbook coding area, not a standard module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

 

[Gord Dibben]

Is it event code?

Where do you have it stored?

Post the code so's someone can maybe trouble-shoot it.


Gord Dibben MS Excel MVP

 

[alexaed]

Is it event code?

Where do you have it stored?

Post the code so's someone can maybe trouble-shoot it.

Gord Dibben MS Excel MVP

Thanks for responding. Here's the code:

Private Sub Workbook_BeforePrint()
msg = MsgBox("Sorry, Company policy prohibits printing or copying this
workbook. All attempts to copy or print this workbook are tracked.
Please close this window.", vbCritical)
Cancel = True
End Sub
'
' Workbook_BeforePrint Macro
' Macro recorded 8/14/2007 by Sherwin-Williams
'
' Keyboard Shortcut: Ctrl+Shift+B
'
It doesn't appear to be stored anywhere. Where should I store it and
how?

Best regards, Ed

 

[alexaed]

Put the following in Workbook coding area, not a standard module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Thanks for responding back to me,
Here's the code:
I believe its accurate, just a matter of storing it in the right spot.
I'm not sure how to do that (I don't have the cancel as boolean in the
code however.

Best regards,
Ed

 

[alexaed]

Put the following in Workbook coding area, not a standard module:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub

Where is the workbook coding area?
Thanks,
Ed

 

[Gary''s Student]

In the Excel Window (NOT VBA) the menu bar usually has:

File Edit View Insert .........

Just to the left of File is a tiny Excel icon. Right-click this icon and
select:
View Code


Then just paste the stuff in.

 

[alexaed]

In the Excel Window (NOT VBA) the menu bar usually has:

File Edit View Insert .........

Just to the left of File is a tiny Excel icon. Right-click this icon and
select:
View Code

Then just paste the stuff in.

That worked. You guys are awesome, Many thanks!

Ed

 

[Gord Dibben]

The code must be stored in the Thisworkbook module, not a general module.

Also must be adjusted as below in order to function.

Private Sub Workbook_BeforePrint(cancel As Boolean)
msg = MsgBox("Sorry, Company policy prohibits printing or copying this" & vbLf _
& "workbook. All attempts to copy or print this workbook are tracked." & vbLf _
& "Please close this window.", vbCritical)
cancel = True
End Sub

With your workbook open right-click on the Excel Icon left of of "File" on the
menu bar.

Select "View Code" Copy/paste the code into that module.

I would suggest finding the other set of code and deleting it.

Probably in a general module.

Alt + F11 to open VBEditor.

Select your workbook/project, expand it and look for a module to remove or clear
the contents from.

Save the workbook.


Gord

 

[alexaed]

The code must be stored in the Thisworkbook module, not a general module.

Also must be adjusted as below in order to function.

Private Sub Workbook_BeforePrint(cancel As Boolean)
msg = MsgBox("Sorry, Company policy prohibits printing or copying this" & vbLf _
& "workbook. All attempts to copy or print this workbook are tracked." & vbLf _
& "Please close this window.", vbCritical)
cancel = True
End Sub

With your workbook open right-click on the Excel Icon left of of "File" on the
menu bar.

Select "View Code" Copy/paste the code into that module.

I would suggest finding the other set of code and deleting it.

Probably in a general module.

Alt + F11 to open VBEditor.

Select your workbook/project, expand it and look for a module to remove or clear
the contents from.

Save the workbook.

Gord

Good info, I did have the code there, thanks, I removed it.

Ed