You problem is that the Excel print dialog is shown after your _BeforePrint
code is run and Excel does not expose an of those settings or an _AfterPrint
event.
1- You could use FindWindow API etc to locate the handle of the dialog (it's
class name is "bosa_sdm_XL9"), but as most of the controls do not expose a
handle (according to Spy++), I doubt it would do you much good.

2- I have a class wrapper to the Windows PrintDialog API (by the KPD Team /
Donald Grover, cannot find a URL at the moment) which looks very similar to
Excel's dialog, but without the "Active Sheet(s)" and "Entire Workbook"
options. As you do not want people to print the Entire Workbook, this may be
suitable.
Otherwise, you can provide a PrintTemplate/PrintHook and make changes to the
dialog. Never done that so can't advise, but I doubt it is trivial.

3- If you go the print queue/jobs route
(http://vb.mvps.org/samples/project.asp?id=PrnInfo), you can only cancel the
job after the user thinks they are printing, which will certainly confuse
the user.

Before you start, you should decide if this is really necessary.

NickHK

"developer281" wrote in message
...
Thanks Tom and Nick,

I read the links that you sent.

The scenario I have is one where I cannot guarantee that the user will
print
the workbook before closing it. If the user doesn't print the workbook
and
he just simply saves it and closes it, I have no opportunity to acccess
the
data that would be sent to the printer's driver.

I was trying to figure out a way to save the print settings before the
workbook gets closed. The reason that I need to gather that data is
because the workbook will be placed in a network folder and opened by a
different computer and automatically printed ... however if the user just
wanted to print a certain # of pages .. versus the whole workbook, then
there
is no way to restrict that in the printout command if I don't know what
the
user would have preferred... and the same applies to the number of copies
as
well as the collate settings.

I didn't want to create my own form where the user would specify the print
settings because it would give the application a weird feeling. The user
(who may or may not be computer savvy) could get confused on whether to
use
the Fileprint versus my custom form, or use both...
moreover, the user would also be using excel for other purposes other than
my application...

If you think of any way that I could get those settings, please let me
know.

Thanks again