Hi there,

I have a feeling the answer to this is going to be "not possible", but
I figured it couldn't hurt to ask.

I have a multi-page Excel workbook. Each spreadsheet in the workbook
has a print CommandButton I've put at the bottom, plus of course the
print and print preview buttons on the Excel toolbar.

Recently I had to add an item to each sheet where some text in the
footer is created on the fly at printout, but only on the first page if
the printout of the worksheet is multiple pages. To accomplish that I
put code behind the print CommandButtons, and in the
Workbook_BeforePrint sub in ThisWorkbook, so it fires for both my print
button and the Excel toolbar buttons.

To avoid having spreadsheets print twice the code in
Workbook_BeforePrint includes a "Cancel=True" at the end, and looks
like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
[Set up LeftFooter]
ActiveWindow.SelectedSheets.Printout From:=1, To:=1, Copies:=1
[Clear LeftFooter]
ActiveWindow.SelectedSheets.Printout From:=2, Copies:=1
Application.EnableEvents = True
Cancel = True
End Sub

However, one thing I neglected to think of is that with this code in
place, if the user selects File-Print, the spreadsheet just prints as
though the user had clicked the Excel toolbar print button, the print
dialog box does not come up.

It's not a critical thing as there are only a couple of the 50+ users
who regularly use the print dialog to change printers, but if there's a
way to actually bring up the print dialog when the user does a
File-Print that would be great.

Any thoughts?

Thanks!

Matt

application.Dialogs(xlDialogPrint).Show

Will show the print dialog, but its the real thing. When you click print,
it prints.

If you just want to select a printer you might use


application.Dialogs(xlDialogPrinterSetup).Show

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
Hi there,

I have a feeling the answer to this is going to be "not possible", but
I figured it couldn't hurt to ask.

I have a multi-page Excel workbook. Each spreadsheet in the workbook
has a print CommandButton I've put at the bottom, plus of course the
print and print preview buttons on the Excel toolbar.

Recently I had to add an item to each sheet where some text in the
footer is created on the fly at printout, but only on the first page if
the printout of the worksheet is multiple pages. To accomplish that I
put code behind the print CommandButtons, and in the
Workbook_BeforePrint sub in ThisWorkbook, so it fires for both my print
button and the Excel toolbar buttons.

To avoid having spreadsheets print twice the code in
Workbook_BeforePrint includes a "Cancel=True" at the end, and looks
like this:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.EnableEvents = False
[Set up LeftFooter]
ActiveWindow.SelectedSheets.Printout From:=1, To:=1, Copies:=1
[Clear LeftFooter]
ActiveWindow.SelectedSheets.Printout From:=2, Copies:=1
Application.EnableEvents = True
Cancel = True
End Sub

However, one thing I neglected to think of is that with this code in
place, if the user selects File-Print, the spreadsheet just prints as
though the user had clicked the Excel toolbar print button, the print
dialog box does not come up.

It's not a critical thing as there are only a couple of the 50+ users
who regularly use the print dialog to change printers, but if there's a
way to actually bring up the print dialog when the user does a
File-Print that would be great.

Any thoughts?

Thanks!

Matt

Just created a macro in the ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
msgbox "Hello world"
End Sub

and it fires just before I choose File|Print or press the PrintPrevie
button.

So I wouldn't understand why yours doesn't work.
Oh, I'm using XL2002

With kind regards,
Ton Teun

--
Ton
-----------------------------------------------------------------------
TonT's Profile: http://www.officehelp.in/member.php?userid=4
View this thread: http://www.officehelp.in/showthread.php?t=64673
Visit - http://www.officehelp.in | http://www.officehelp.in/index/index.php

Thanks Tom, I'll give that a shot.

Is there any way for the code in Workbook_BeforePrint to recognize
whether it's being called from the print or print preview button or a
File-Print call so I can try to fire the code to show the print dialog
only when the user has gone through File-Print?

Thanks!

Matt

Beforeprint fires before the dialog is displayed and you are using cancel,
so it should not display on its own. It seems to me the only time you
wouldn't want to show it is if the print button is clicked. However, even
if you put it up then, it would only be a minor annoyance I would think.

Now print preview is probably a problem. I don't know of a way to
differentiate. Perhaps you could again annoy your users by asking.



--
Regards,
Tom Ogilvy



wrote in message
ups.com...
Thanks Tom, I'll give that a shot.

Is there any way for the code in Workbook_BeforePrint to recognize
whether it's being called from the print or print preview button or a
File-Print call so I can try to fire the code to show the print dialog
only when the user has gone through File-Print?

Thanks!

Matt

I'll have to decide if the print dialog is important enough to justify
the time to play around with the code to make it work. After
overcoming the problems of having the footer only on page 1 of
multipage printouts and preventing sheets from printing twice it might
not be worth the time to try and make another wrinkle work!

Thanks again for the input, Tom... Much appreciated!

Matt