Preventing printing

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
 
B

Bernie Deitrick

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Preventing printing 3
Cancel printing a worksheet 1
Cancel printing a worksheet 0
Printing Macro 1
print from macro, using if formula 4
Printing Macro 4
Pop Up Message before printing 3
Command Button 6

Top