How to create pop-up message in Excel before Printing

[ajay.pol@gmail.com]

Hi All,

I have a huge file that consumes reams of paper when printed. So I want
a message box to appear when people click on the print icon in Excel
(or click on File >> Print or Ctrl-P), suggesting that they should only
print relevant sections of the document.

Can someone suggest some basic macro code to achieve this?

Thanks!
123champ

[Kevin B]

YOu can use the Workbook_BeforePrint event, which is activate before print
preview or before the document is sent to the printer.
--
Kevin Backmann


"ajay.pol@gmail.com" wrote:

> Hi All,
>
> I have a huge file that consumes reams of paper when printed. So I want
> a message box to appear when people click on the print icon in Excel
> (or click on File >> Print or Ctrl-P), suggesting that they should only
> print relevant sections of the document.
>
> Can someone suggest some basic macro code to achieve this?
>
> Thanks!
> 123champ
>
>

[Otto Moehrbach]

You would use a Workbook_BeforePrint macro for that. You can write code in
that macro telling the user whatever you want. You can also cancel the
print command in the same macro. Here is one suggested code:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim Ans As Long
Ans = MsgBox("It is recommended that you print only a portion" & Chr(13)
& _
"of this workbook and not the entire workbook." & Chr(13) & _
"Do you want this print job to continue?", 4, "Continue Print?")
If Ans = vbNo Then Cancel = True
End Sub
HTH Otto
<ajay.pol@gmail.com> wrote in message
news:1142890814.275802.87650@z34g2000cwc.googlegroups.com...
> Hi All,
>
> I have a huge file that consumes reams of paper when printed. So I want
> a message box to appear when people click on the print icon in Excel
> (or click on File >> Print or Ctrl-P), suggesting that they should only
> print relevant sections of the document.
>
> Can someone suggest some basic macro code to achieve this?
>
> Thanks!
> 123champ
>

[Kevin B]

This is a bit more illustrative than my previous post:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

Dim YesNo As Integer

YesNo = MsgBox("Only print relevant data. Do you wish " & _
"to continue?", vbYesNo, "Yo! What's Up")

If YesNo = vbNo Then
Cancel = True
End If

End Sub

Press Alt+F11 to open the VB editor and if necessary press Ctrl+R to open
the Project window. In the project window locate the VBA project for the
current file and double click on the ThisWorkbook object (you might have to
double click your way through the tree to display everything).

In the code window on the right, drop down the list at the top of the screen
that currently says General and select workbook, and then to the right of
that, drop down the list and select the BeforePrint event. The code goes
right here.
--
Kevin Backmann


"ajay.pol@gmail.com" wrote:

> Hi All,
>
> I have a huge file that consumes reams of paper when printed. So I want
> a message box to appear when people click on the print icon in Excel
> (or click on File >> Print or Ctrl-P), suggesting that they should only
> print relevant sections of the document.
>
> Can someone suggest some basic macro code to achieve this?
>
> Thanks!
> 123champ
>
>

[ajay.pol@gmail.com]

Hi Kevin,

That worked beautifully!!
Thanks for the code, and for saving trees!

cheers
123champ