[jatman]

i had the following macro that worked in Office 2003, but in Office 2007 it
does not work. It prevents the print job once only, but the user can just
press the print again and it prints...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "DSR" Then
Cancel = True
Application.EnableEvents = False
With ActiveSheet
If .Range("d1") < "[Ctrl] ;" And Range("d57") = 0# And
Range("d5") < 0# Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Call Mail
End If
End With
End If
End Sub

any suggestions to help prevent the print job would be appreciated.

thank you,

jat

[Jim Cone[_2_]]

It didn't work in XL2003 either.
What you have is an "Event" sub.
If XL is told to ignore events then all event subs are inoperative.
That instruction remains in effect until XL is told to recognize events again or XL is restarted.
Check out "EnableEvents" in VBA help.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"jatman"
wrote in message
i had the following macro that worked in Office 2003, but in Office 2007 it
does not work. It prevents the print job once only, but the user can just
press the print again and it prints...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "DSR" Then
Cancel = True
Application.EnableEvents = False
With ActiveSheet
If .Range("d1") < "[Ctrl] ;" And Range("d57") = 0# And
Range("d5") < 0# Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Call Mail
End If
End With
End If
End Sub

any suggestions to help prevent the print job would be appreciated.
thank you,
jat

[Jon Peltier]

Before the last End If, insert this line:

Application.EnableEvents = True

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"jatman" wrote in message
...
i had the following macro that worked in Office 2003, but in Office 2007 it
does not work. It prevents the print job once only, but the user can just
press the print again and it prints...

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "DSR" Then
Cancel = True
Application.EnableEvents = False
With ActiveSheet
If .Range("d1") < "[Ctrl] ;" And Range("d57") = 0# And
Range("d5") < 0# Then
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Call Mail
End If
End With
End If
End Sub

any suggestions to help prevent the print job would be appreciated.

thank you,

jat