macro does not work in Office 2007... suggestions:

  • Thread starter Thread starter jatman
  • Start date Start date
J

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 Subany suggestions to help prevent the print job would be appreciated.

thank you,

jat
 
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 Subany suggestions to help prevent the print job would be appreciated.
thank you,
jat
 
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 said:
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 Subany suggestions to help prevent the print job would be appreciated.

thank you,

jat
Click to expand...
 
Back
Top