VBA BeforePrint MsgBox

brow1010

New Member
Joined
Jul 10, 2017
Messages
2
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

Well-known Member
Joined
Oct 15, 2013
Messages
1,120
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:
Code:
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

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,556
Office Version
2013
Platform
Windows
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.
 
Last edited:

Forum statistics

Threads
1,082,552
Messages
5,366,286
Members
400,882
Latest member
zaldy_a

Some videos you may like

This Week's Hot Topics

Top