VBA BeforePrint MsgBox

[brow1010]

I have a form that people fill out, print, and scan to me. I want to set a macro so that when they hit print, it pops up a message box that reminds them I need their cover page and calculations pages. I have read dozens of help forum posts and copied many of the codes into sample excel sheets to try to get them to work and nothing happens. I have macros enabled. This is the code I currently have in my workbook. It is the one I have gotten closest with. It will pop up the message and if they hit "no" it will cancel the print, but if they hit "yes" it will pop up run-time error 438 (object doesn't support this property or method). Then when I try to debug it and open the VBA editor, even if I don't make any changes, the message won't even pop up the next time I print. I have to close out of excel and open it back up.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Confirm As VbMsgBoxResult
Application.EnableEvents = False
Cancel = True
Confirm = MsgBox("Reminder: Your underwriter needs both the cover page and any calculations pages. Continue?", vbYesNo)
If Confirm = vbYes Then ThisWorkbook.Print
Application.EnableEvents = True
End Sub

 

[CalcSux78]

Welcome to the forum!

With your sub, "the bullet is in the chamber." The print job is already scheduled. When you add Cancel = True, this aborts the print. Your error is because the print command cannot be called from the BeforePrint event.

Try:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim Confirm As VbMsgBoxResult
With Application
    .EnableEvents = 0
    Confirm = MsgBox("Reminder: Your underwriter needs both the cover page and any calculations pages. Continue?", vbYesNo)
    If Not Confirm = vbYes Then Cancel = True
    .EnableEvents = 1
End With
End Sub

 

[xenou]

The number of times I've printed a workbook is probably zero but I would guess in this one you don't need to disable events / re-enable events.

But in general yes the problem is an infinite loop created by calling print inside the before print event, which triggers a before print event ... and over and over. I.e., in the original posted code. ... if vba just doesn't allow that then it's a good catch by the compiler.

 

[brow1010]

Amazing! Thank you so much!!