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" wrote in message
...
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

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" wrote in message
...
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" wrote in message
...
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

Chris,
Well, I leant something today.

What does "print_what" expect as a value ?

NickHK

"Chris Lewis" wrote in message
...

"DanielWalters6" wrote in
message
...
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" wrote in message
...
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