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
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.
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
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top