I have set up a macro that will print dozens of pages after selecting the
colour printer in the office. This is done by specifying the exact printer in
the macro itself.

I want to send this to people in different locations (these people are not
very clued up on excel). I want to allow them to print but obviously the
printer will be different.

Is there a way I can bring up a drop down box that will allow them to select
their desired printer?

If so what would be the coding (I am only just getting into writing macros
myself).

Thanks in advance.

Tap into the windows print dialog

application.Dialogs(xlDialogPrint).Show

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"fullers" wrote in message
...
I have set up a macro that will print dozens of pages after selecting the
colour printer in the office. This is done by specifying the exact printer
in
the macro itself.

I want to send this to people in different locations (these people are not
very clued up on excel). I want to allow them to print but obviously the
printer will be different.

Is there a way I can bring up a drop down box that will allow them to
select
their desired printer?

If so what would be the coding (I am only just getting into writing macros
myself).

Thanks in advance.

Thanks,

What my program actually does is alters the same worksheet and then prints
each time.

The current macro I have is this:

Sub PrintColour()

For i = 1 To j

Range("AF1").Select
ActiveCell.Value = i
Application.Run "'Mystery Shop.xls'!ReOrder"
Application.ActivePrinter = "\\prnl027s\PR000269 on Ne09:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\prnl027s\PR000269 on Ne09:", Collate:=True

Next i
End Sub

This changes the graph that appears on the screen. What I want to be able to
do is have the user select what printer to use just once and it print it all
on the same one.
What would I need to change to allow this to happen?

Thanks in advance.

"Bob Phillips" wrote:

Tap into the windows print dialog

application.Dialogs(xlDialogPrint).Show

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"fullers" wrote in message
...
I have set up a macro that will print dozens of pages after selecting the
colour printer in the office. This is done by specifying the exact printer
in
the macro itself.

I want to send this to people in different locations (these people are not
very clued up on excel). I want to allow them to print but obviously the
printer will be different.

Is there a way I can bring up a drop down box that will allow them to
select
their desired printer?

If so what would be the coding (I am only just getting into writing macros
myself).

Thanks in advance.

On the first time through, throw up the dialog and get the user to hit the
print button. Subsequent time, you can just do Printout, the printer will
remain selected.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"fullers" wrote in message
...
Thanks,

What my program actually does is alters the same worksheet and then prints
each time.

The current macro I have is this:

Sub PrintColour()

For i = 1 To j

Range("AF1").Select
ActiveCell.Value = i
Application.Run "'Mystery Shop.xls'!ReOrder"
Application.ActivePrinter = "\\prnl027s\PR000269 on Ne09:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"\\prnl027s\PR000269 on Ne09:", Collate:=True

Next i
End Sub

This changes the graph that appears on the screen. What I want to be able
to
do is have the user select what printer to use just once and it print it
all
on the same one.
What would I need to change to allow this to happen?

Thanks in advance.

"Bob Phillips" wrote:

Tap into the windows print dialog

application.Dialogs(xlDialogPrint).Show

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"fullers" wrote in message
...
I have set up a macro that will print dozens of pages after selecting
the
colour printer in the office. This is done by specifying the exact
printer
in
the macro itself.

I want to send this to people in different locations (these people are
not
very clued up on excel). I want to allow them to print but obviously
the
printer will be different.

Is there a way I can bring up a drop down box that will allow them to
select
their desired printer?

If so what would be the coding (I am only just getting into writing
macros
myself).

Thanks in advance.