Preventing printing

  • Thread starter Thread starter ordnance1
  • Start date Start date
O

ordnance1

I want to prevent ptinting of my workbook without using the macro I
have set up for printing. I entered the code below to prevent
printing, but it also prevents my macro from printing. Any ideas how I
can get around this, or it an all or nothing kind of thing? Also below
is my print macro.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
msg = MsgBox("Sorry, printing is disabled for this workbook.",
vbCritical)
Cancel = True
End Sub



Private Sub CommandButton2_Click()

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Module2.SortSunday

ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

Module2.UnSort_AllDays

Unload UserFormPrinting_Sunday

End Sub
 
At the top of a regular code module, declare a public Boolean

Public AllowPrint As Boolean

Then change your code like this:

Private Sub Workbook_Open()
AllowPrint = False
End Sub

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If AllowPrint Then Exit Sub
msg = MsgBox("Sorry, printing is disabled for this workbook.",
vbCritical)
Cancel = True
End Sub

Private Sub CommandButton2_Click()
AllowPrint = True
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Module2.SortSunday
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Module2.UnSort_AllDays
Unload UserFormPrinting_Sunday
AllowPrint = False
End Sub

HTH,
Bernie
MS Excel MVP
 
Back
Top