Can I only allow printing to PDF in Excel?

Sorry, should have been clearer...

Code I posted (by right clicking on the sheet tab, selecting "view code"
etc) was:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PrintOut copies:=1, ActivePrinter:="Acrobat Distiller on Ne10:"
End Sub

Basically, when I say it doesn't work, I mean that when I print (by
selecting file-print, or Ctrl-P etc), it doesn't set the printer to be
"Acrobat Distiller on Ne10:", it leaves it as the default printer.

To try to see if I was missing something I recorded a macro to simply print
to PDF and got this:

Sub print2PDF()
Application.ActivePrinter = "Acrobat Distiller on Ne10:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"Acrobat Distiller on Ne10:"
End Sub

This macro prints to PDF when I run it, but of course has to be run, rather
than automatically changing the settings for the user when they try to print.
(I can of course make the shortcut for this marco ctrl+p, but then if the
user tries to print by another route, they by-pass these settings.)

Out of interest, at what stage should I expect the change to take effect,
i.e. what is the trigger to effect the change?

I would also like to automatically change the printer before print preview.

Hope that's clearer now!
Thanks.


"Gord Dibben" wrote:

> Post the code that doesn't work after you changed the printer name.
>
> And describe what "doesn't work" means to you.
>
>
> Gord Dibben MS Excel MVP
>
> On Tue, 16 Sep 2008 10:18:10 -0700, nanook
> <nanook RemoveThis @discussions.microsoft.com> wrote:
>
> >I have created a template in Excel which has been set up so that the layout
> >is perfect when printing to PDF (which is how the document will mostly be
> >used) but the layout changes if printing direct to our printer. Is there a
> >way that I can ONLY allow printing to PDF from this document?
> >
> >I asked this question in another forum (to which the link I saved now won't
> >work!) some time ago and got the following suggestion (thanks papou):
> >
> >You may consider using some VBA to achieve this.
> >One way is to use the Workbook_BeforePrint event and specify the pdf printer
> >in the PrintOut method, eg:
> >
> >Private Sub Workbook_BeforePrint(Cancel As Boolean)
> >ActiveSheet.PrintOut copies:=1, ActivePrinter:="CutePDF Writer on CPW2:"
> >End Sub
> >
> >Please note you will need to amend the **activeprinter** name part of my
> >code to match precisely your pdf printer description.
> >
> >-----
> >
> >Now I tried using this code, but it didn't work (and yes, I changed the code
> >to match my printer description).
> >
> >Any other suggestions / modifications?
> >Many thanks
>
>

Workbook_BeforePrint event is run from Thisworkbook module, not a sheet
module.

Move your code from the sheet and stick it in the appropriate module.

Right-click on the Excel Icon left of "File" and "View Code.

You will be in Thisworkbook module.


Gord

On Wed, 17 Sep 2008 02:39:01 -0700, nanook
<nanook RemoveThis @discussions.microsoft.com> wrote:

>Sorry, should have been clearer...
>
>Code I posted (by right clicking on the sheet tab, selecting "view code"
>etc) was:
>
>Private Sub Workbook_BeforePrint(Cancel As Boolean)
>ActiveSheet.PrintOut copies:=1, ActivePrinter:="Acrobat Distiller on Ne10:"
>End Sub
>
>Basically, when I say it doesn't work, I mean that when I print (by
>selecting file-print, or Ctrl-P etc), it doesn't set the printer to be
>"Acrobat Distiller on Ne10:", it leaves it as the default printer.
>
>To try to see if I was missing something I recorded a macro to simply print
>to PDF and got this:
>
>Sub print2PDF()
> Application.ActivePrinter = "Acrobat Distiller on Ne10:"
> ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
> "Acrobat Distiller on Ne10:"
>End Sub
>
>This macro prints to PDF when I run it, but of course has to be run, rather
>than automatically changing the settings for the user when they try to print.
>(I can of course make the shortcut for this marco ctrl+p, but then if the
>user tries to print by another route, they by-pass these settings.)
>
>Out of interest, at what stage should I expect the change to take effect,
>i.e. what is the trigger to effect the change?
>
>I would also like to automatically change the printer before print preview.
>
>Hope that's clearer now!
>Thanks.
>
>
>"Gord Dibben" wrote:
>
>> Post the code that doesn't work after you changed the printer name.
>>
>> And describe what "doesn't work" means to you.
>>
>>
>> Gord Dibben MS Excel MVP
>>
>> On Tue, 16 Sep 2008 10:18:10 -0700, nanook
>> <nanook RemoveThis @discussions.microsoft.com> wrote:
>>
>> >I have created a template in Excel which has been set up so that the layout
>> >is perfect when printing to PDF (which is how the document will mostly be
>> >used) but the layout changes if printing direct to our printer. Is there a
>> >way that I can ONLY allow printing to PDF from this document?
>> >
>> >I asked this question in another forum (to which the link I saved now won't
>> >work!) some time ago and got the following suggestion (thanks papou):
>> >
>> >You may consider using some VBA to achieve this.
>> >One way is to use the Workbook_BeforePrint event and specify the pdf printer
>> >in the PrintOut method, eg:
>> >
>> >Private Sub Workbook_BeforePrint(Cancel As Boolean)
>> >ActiveSheet.PrintOut copies:=1, ActivePrinter:="CutePDF Writer on CPW2:"
>> >End Sub
>> >
>> >Please note you will need to amend the **activeprinter** name part of my
>> >code to match precisely your pdf printer description.
>> >
>> >-----
>> >
>> >Now I tried using this code, but it didn't work (and yes, I changed the code
>> >to match my printer description).
>> >
>> >Any other suggestions / modifications?
>> >Many thanks
>>
>>