Hello, through code I have made Excel print a particular worksheet (defined
range)

Is there any way that I can make the windows printer dialogue box appear.
(I would like to select which printer it prints to each time)

I believe I could work out how to do this, given time.

However I would like to be able to specify that "2" appears in the number of
copies box.

(One copy for the customer to takeaway and a hard copy for my records)

TIA for any help! I'm in a bit of a pickle clicking the print button twice,
and then maybe a third time to just confirm. :-)


--
Dan Walters

Daniel,
This will show the print dialog:
Application.Dialogs(xlDialogPrint).Show

But then it is difficult to control the print dialog properties once it
shown.

Why not show a list of printers :
http://word.mvps.org/FAQS/MacrosVBA/...lePrinters.htm

Then use the selected printer in the .PrintOut method, setting the copies=2

NickHK

"DanielWalters6" <(E-Mail Removed)> wrote in message
newsE682B7B-450B-47DC-A760-(E-Mail Removed)...
> Hello, through code I have made Excel print a particular worksheet
(defined
> range)
>
> Is there any way that I can make the windows printer dialogue box appear.
> (I would like to select which printer it prints to each time)
>
> I believe I could work out how to do this, given time.
>
> However I would like to be able to specify that "2" appears in the number
of
> copies box.
>
> (One copy for the customer to takeaway and a hard copy for my records)
>
> TIA for any help! I'm in a bit of a pickle clicking the print button
twice,
> and then maybe a third time to just confirm. :-)
>
>
> --
> Dan Walters

"DanielWalters6" <(E-Mail Removed)> wrote in message
newsE682B7B-450B-47DC-A760-(E-Mail Removed)...
> Hello, through code I have made Excel print a particular worksheet
> (defined
> range)
>
> Is there any way that I can make the windows printer dialogue box appear.
> (I would like to select which printer it prints to each time)
>
> I believe I could work out how to do this, given time.
>
> However I would like to be able to specify that "2" appears in the number
> of
> copies box.
>
> (One copy for the customer to takeaway and a hard copy for my records)
>
> TIA for any help! I'm in a bit of a pickle clicking the print button
> twice,
> and then maybe a third time to just confirm. :-)
>
>
> --
> Dan Walters

You can use

Application.Dialogs(xlDialogPrint).Show ,,,2

The commas are to indicate the required parameter position from the
following list

range_num, from, to, copies, draft, preview, print_what, color, feed,
quality, y_resolution, selection, printer_text, print_to_file, collate

So for 5 copies end with ,,,5

--
Chris Lewis

Thaknyou for your help - I will let you know how I get on with this project!


--
Dan Walters


"NickHK" wrote:

> Daniel,
> This will show the print dialog:
> Application.Dialogs(xlDialogPrint).Show
>
> But then it is difficult to control the print dialog properties once it
> shown.
>
> Why not show a list of printers :
> http://word.mvps.org/FAQS/MacrosVBA/...lePrinters.htm
>
> Then use the selected printer in the .PrintOut method, setting the copies=2
>
> NickHK
>
> "DanielWalters6" <(E-Mail Removed)> wrote in message
> newsE682B7B-450B-47DC-A760-(E-Mail Removed)...
> > Hello, through code I have made Excel print a particular worksheet
> (defined
> > range)
> >
> > Is there any way that I can make the windows printer dialogue box appear.
> > (I would like to select which printer it prints to each time)
> >
> > I believe I could work out how to do this, given time.
> >
> > However I would like to be able to specify that "2" appears in the number
> of
> > copies box.
> >
> > (One copy for the customer to takeaway and a hard copy for my records)
> >
> > TIA for any help! I'm in a bit of a pickle clicking the print button
> twice,
> > and then maybe a third time to just confirm. :-)
> >
> >
> > --
> > Dan Walters
>
>
>

Chris,
Well, I leant something today.

What does "print_what" expect as a value ?

NickHK

"Chris Lewis" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> "DanielWalters6" <(E-Mail Removed)> wrote in
message
> newsE682B7B-450B-47DC-A760-(E-Mail Removed)...
> > Hello, through code I have made Excel print a particular worksheet
> > (defined
> > range)
> >
> > Is there any way that I can make the windows printer dialogue box
appear.
> > (I would like to select which printer it prints to each time)
> >
> > I believe I could work out how to do this, given time.
> >
> > However I would like to be able to specify that "2" appears in the
number
> > of
> > copies box.
> >
> > (One copy for the customer to takeaway and a hard copy for my records)
> >
> > TIA for any help! I'm in a bit of a pickle clicking the print button
> > twice,
> > and then maybe a third time to just confirm. :-)
> >
> >
> > --
> > Dan Walters
>
> You can use
>
> Application.Dialogs(xlDialogPrint).Show ,,,2
>
> The commas are to indicate the required parameter position from the
> following list
>
> range_num, from, to, copies, draft, preview, print_what, color, feed,
> quality, y_resolution, selection, printer_text, print_to_file, collate
>
> So for 5 copies end with ,,,5
>
> --
> Chris Lewis
>
>

Thankyou, that's brilliant, does exactly what I want, and with your added bit
of info and the response from NickHK's further question, I've managed to
reuse the code in other places within my project. (I have also advised an
friend who was happy to find there was an easier way!)

Thankyou once again
--
Dan Walters


"Chris Lewis" wrote:

>
> "DanielWalters6" <(E-Mail Removed)> wrote in message
> newsE682B7B-450B-47DC-A760-(E-Mail Removed)...
> > Hello, through code I have made Excel print a particular worksheet
> > (defined
> > range)
> >
> > Is there any way that I can make the windows printer dialogue box appear.
> > (I would like to select which printer it prints to each time)
> >
> > I believe I could work out how to do this, given time.
> >
> > However I would like to be able to specify that "2" appears in the number
> > of
> > copies box.
> >
> > (One copy for the customer to takeaway and a hard copy for my records)
> >
> > TIA for any help! I'm in a bit of a pickle clicking the print button
> > twice,
> > and then maybe a third time to just confirm. :-)
> >
> >
> > --
> > Dan Walters
>
> You can use
>
> Application.Dialogs(xlDialogPrint).Show ,,,2
>
> The commas are to indicate the required parameter position from the
> following list
>
> range_num, from, to, copies, draft, preview, print_what, color, feed,
> quality, y_resolution, selection, printer_text, print_to_file, collate
>
> So for 5 copies end with ,,,5
>
> --
> Chris Lewis
>
>
>