I need to protect the worksheet from being printed out using the most
obvious printing controls. Disabling the toolbar printing icon was
easy, as was blocking the ctrl-P key combination but how to disable the
print item in the File menu??
The VB property for CommandBar.Controls which could manipulate the
submenu items is not documented.

thanks for help, Harri

in excel 2003TOOLSCUSTOMISECOMMAND TAB THEN CLICK REARRAGE COMMANDS
THEN SIMPLY DELETE PRINT COMMANDS.

"Harri Lumme" wrote:

I need to protect the worksheet from being printed out using the most
obvious printing controls. Disabling the toolbar printing icon was
easy, as was blocking the ctrl-P key combination but how to disable the
print item in the File menu??
The VB property for CommandBar.Controls which could manipulate the
submenu items is not documented.

thanks for help, Harri

In the VBA editor insert a new class module into your workbook called Class1
and include the following code:-

Dim WithEvents App As Application
Private Sub Class_Initialize()
Set App = Application
End Sub
Private Sub Class_Terminate()
Set App = Nothing
End Sub
Private Sub App_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean)
Cancel = True
End Sub

Now insert a new module and include the following code:-

Dim MyClass As Class1
Public Sub Auto_Open()
Set MyClass = New Class1
End Sub
Public Sub Auto_Close()
Set MyClass = Nothing
End Sub

It cancels any attempt to print from whatever option within Excel. It
obviously doesn't stop a user doing a print screen though.


--
Alan Moseley IT Consultancy
http://www.amitc.co.uk


"Harri Lumme" wrote:

I need to protect the worksheet from being printed out using the most
obvious printing controls. Disabling the toolbar printing icon was
easy, as was blocking the ctrl-P key combination but how to disable the
print item in the File menu??
The VB property for CommandBar.Controls which could manipulate the
submenu items is not documented.

thanks for help, Harri

Thanks Gord,
This was basically the solution I was looking for.
Disabling macros would not be a loophole, since I have the page content
being created with another macro and by disabling macros it would not
work either (I assume).

So what I did was to declare a global boolean 'Using_macro = FALSE'.
This is then changed to TRUE in my page creation and printout macro.
After the printout the same macro returns the 'Using_macro' to FALSE.

Finally I added the condition clause to your suggestion:
---
Cancel = True
if Using_macro then
Cancel = False
end if
---
The MsgBox was not necessary in my case.

thanks again,
Harri


Just for one workbook?

Stick this code in Thisworkbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "no printing allowed"
Cancel = True
End Sub

Note: users can always circumvent by disabling macros on open.


Gord Dibben MS Excel MVP

On Tue, 21 Oct 2008 11:18:18 +0300, Harri Lumme <harri@pharmatorydotcom
wrote:

I need to protect the worksheet from being printed out using the most
obvious printing controls. Disabling the toolbar printing icon was
easy, as was blocking the ctrl-P key combination but how to disable the
print item in the File menu??
The VB property for CommandBar.Controls which could manipulate the
submenu items is not documented.

thanks for help, Harri

Good to hear you're sorted out.

Thanks for the added info and method.


Gord

On Thu, 06 Nov 2008 10:32:26 +0200, Harri Lumme <harri@pharmatorydotcom
wrote:

Thanks Gord,
This was basically the solution I was looking for.
Disabling macros would not be a loophole, since I have the page content
being created with another macro and by disabling macros it would not
work either (I assume).

So what I did was to declare a global boolean 'Using_macro = FALSE'.
This is then changed to TRUE in my page creation and printout macro.
After the printout the same macro returns the 'Using_macro' to FALSE.

Finally I added the condition clause to your suggestion:
---
Cancel = True
if Using_macro then
Cancel = False
end if
---
The MsgBox was not necessary in my case.

thanks again,
Harri


Just for one workbook?

Stick this code in Thisworkbook module.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox "no printing allowed"
Cancel = True
End Sub

Note: users can always circumvent by disabling macros on open.


Gord Dibben MS Excel MVP

On Tue, 21 Oct 2008 11:18:18 +0300, Harri Lumme <harri@pharmatorydotcom
wrote:

I need to protect the worksheet from being printed out using the most
obvious printing controls. Disabling the toolbar printing icon was
easy, as was blocking the ctrl-P key combination but how to disable the
print item in the File menu??
The VB property for CommandBar.Controls which could manipulate the
submenu items is not documented.

thanks for help, Harri