BeforePrint only running once

J

jasminesy

I have a workbook with a sheet name "Data" (among others) if the "Data" sheet
is selected and the user tries to print then I am running the following Macro:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
MsgBox ("The Print Preview will be viewed first." & Chr(10) & "Click the
close button when finished reviewing.")
If ActiveSheet.Name <> "Data" Then
Exit Sub
Else:
Application.EnableEvents = False
ActiveSheet.Unprotect Password:="missy"
Rows("3:3").Select
Selection.EntireRow.Hidden = True
With ActiveSheet
.PrintPreview
Cancel = True
End With
dsa = MsgBox("Continue with Print?", vbYesNo)
If dsa = vbNo Then
Rows("3:3").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="missy"
Range("a1").Select
Exit Sub
End If
MsgBox ("Select your printer" & Chr(10) & "name and click okay")
Application.Dialogs(xlDialogPrinterSetup).Show
Application.ActiveSheet.PrintOut
Application.EnableEvents = True
Rows("3:3").Select
Selection.EntireRow.Hidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True, Password:="missy"
Range("a1").Select
End If
End Sub


The problem is: If I select no (at "continue Printing?") and then try to
print again it goes directly to the print dialog box not thrugh the macro.

Have I messed something up???
 
J

JE McGimpsey

If you select No, you exit the sub without executing

Application.EnableEvents = True

so your subsequent print commands don't fire the BeforePrint event.
 
J

jasminesy

When I put the Application.EnableEvents = True before the exit sub also it
worked perfect.

Thank You so much...
 

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

Top