Print workbook - disable

[Ruckley]

Hi,

I need some VBA code to disable the printing of a whole excel workbook, but leave the capability to print a single sheet. Is this possible?

many thanks.

Tony.

[mancubus]

try:
in ThisWokbook's code module:
(reminder: if macros are disabled, does not work)


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "print" Then 'adjust sheet name
Cancel = True
End If
Next
End Sub

[Ruckley]

Thanks for your help. This seems to work in a way but I would really like the user to be able to see the Excel print form, with all its options, and for the option button to print 'Entire Workbook' to be disabled.

It seems as though it ought to be an easy thing to do...

[mancubus]

this code directly prints the sheet named "print" only.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
'http://www.ozgrid.com/forum/showthread.php?t=64601
Dim ws As Variant

Application.DisplayAlerts = False
Application.EnableEvents = False

For Each ws In ThisWorkbook.Worksheets
If ws.Name = "print" Then
ws.PrintOut
End If
Next ws

Application.DisplayAlerts = True
Application.EnableEvents = True

Cancel = True

End Sub