xlDialogPrint

[doodles82a]

Good afternoon. I used VBA to format the pages I want printed.
However, when they press the Print icon I developed for them, I want
the xlDialogPrint to appear so they can select an appropriate printer.
The xlDialogPrint box appears, but I only want them to use the dialog
box to select a printer. Once they select the printer and press OK,
the worksheets will print based on the print definitions in the VBA. I
don't want them to be able to adjust any of the other arguments, as
I've already defined those arguments in the VBA code. Is it possible
for me to use VBA code to disable all arguments of the xlDialogPrint
box, except the printer selection argument? Thanks for your time.

Michael

 

[Jim Rech]

Application.Dialogs(xlDialogPrinterSetup).Show

--
Jim
| Good afternoon. I used VBA to format the pages I want printed.
| However, when they press the Print icon I developed for them, I want
| the xlDialogPrint to appear so they can select an appropriate printer.
| The xlDialogPrint box appears, but I only want them to use the dialog
| box to select a printer. Once they select the printer and press OK,
| the worksheets will print based on the print definitions in the VBA. I
| don't want them to be able to adjust any of the other arguments, as
| I've already defined those arguments in the VBA code. Is it possible
| for me to use VBA code to disable all arguments of the xlDialogPrint
| box, except the printer selection argument? Thanks for your time.
|
| Michael

 

[ward376]

Maybe you could create your own dialog that only lists available
printers...

http://support.microsoft.com/kb/q166008/

Cliff Edwards

 

[Dave Peterson]

How about using a different dialog?

Application.Dialogs(xlDialogPrinterSetup).Show

 

[doodles82a]

Application.Dialogs(xlDialogPrinterSetup).Show

--

| Good afternoon. I used VBA to format the pages I want printed.
| However, when they press the Print icon I developed for them, I want
| the xlDialogPrint to appear so they can select an appropriate printer.
| The xlDialogPrint box appears, but I only want them to use the dialog
| box to select a printer. Once they select the printer and press OK,
| the worksheets will print based on the print definitions in the VBA. I
| don't want them to be able to adjust any of the other arguments, as
| I've already defined those arguments in the VBA code. Is it possible
| for me to use VBA code to disable all arguments of the xlDialogPrint
| box, except the printer selection argument? Thanks for your time.
|
| Michael

Thanks, Jim. The xlDialogPrinterSetup allows me to select a printer.
Once I press OK, though, the worksheets do not print.

 

[Dave Peterson]

Have your code print what you want.

Activesheet.printout
'or
ActiveSheet.Range("a1:x99").PrintOut
'or
activewindow.SelectedSheets.printout

 

[Jim G]

Jim/Dave, Thanks guys.

This solved a problem for me. However, when the cancel button is press it
still prints. Is there a way to have the print request cancelled. Here's
what I have

Sub PrintSelection()
'
' Print Selected Range Macro
'

Dim LastRow As Long

Application.ScreenUpdating = False

With Worksheets("Jobs")
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
End With


Application.Dialogs(xlDialogPrinterSetup).Show

ActiveSheet.Range("A3" & LastRow).PrintOut

Range("A3").Select

Application.ScreenUpdating = True
End Sub

Cheers

 

[Dave Peterson]

The cancel button tells you that the user didn't want to change printers--not
that they didn't want to print.

Maybe you could show this dialog and remove your .printout statement:

Application.Dialogs(xlDialogPrint).Show

Or you could ask:

dim resp as long
....
Application.Dialogs(xlDialogPrinterSetup).Show
resp = Msgbox(Prompt:="still want to print", buttons:=vbyesno)
if resp = vbyes then
ActiveSheet.Range("A3" & LastRow).PrintOut
end if

 

[Jim G]

Thanks Dave, as usual, perfectly simple.

I haven't run it in the office yet but the cancel dialog was just what I was
looking for.

 

[Jim G]

Thanks Cliff,
I only have a simple P to P network this time around so this might be a bit
of overkill. However, this in an interesting article I'll file for future
reference. Thanks for the link.