Is there a way to set the printer's properties using VBA?

[daniels012]

Is there a way to set the printer's properties using VBA?

Sometimes we set the printer for BEST quality to do photos or brochures. Well if we don't change it back, when we go to print a spreadsheet, it takes forever because it is set on best quality.

So......
I want to be able to set the print properties to normal using VBA.
If this is impossible, please let me know that too.....

Thank You,
Michael

 

[daniels012]

bump

 

[VoG]

I'll be happy to learn otherwise but I'm not sure that you can. A kind of halfway house is to display the printer dialog then click the Properties button, like this:

Sub test()
Dim bResponse As Boolean
bResponse = Application.Dialogs(xlDialogPrinterSetup).Show
If bResponse = False Then
    MsgBox "User cancelled"
    Exit Sub
End If
' Rest of code here
End Sub

 

[daniels012]

VoG II,
I appreciate your input as always
I did find this:

Private Sub Worksheet_Activate()
     MsgBox "Horizontal Print Quality is " & _
    Worksheets("Sheet1").PageSetup.PrintQuality(1) & "  Horizontal Print Quality is " & _
    Worksheets("Sheet1").PageSetup.PrintQuality(2)
End Sub

Which this shows the Print Quality (vertical and Horizonal) in a message box.

I would like the ability to change the Best value (600) to the Normal value (300).

not sure how to get my code to do this.

I tried in a module:

Sub Macro1()
    With ActiveSheet
    .PageSetup.PrintQuality(1) = 300
    .PageSetup.PrintQuality(2) = 300
    End With
End Sub

It does not change it though.

Michael

 

[daniels012]

ideas?
I waited 2 days before bumping in hopes that someone may be able to help.

Michael

 

[Greg Truby]

Michael,

This appears to work in testing:

Sub TogglePrintQuality()
    With ActiveSheet.PageSetup
        .PrintQuality = IIf(.PrintQuality(1) = 600, 300, 600)
    End With
End Sub

 

[daniels012]

Greg,
Thank you for your code!
However, when testing it still prints Best. I am trying to get it to print Normal.

When I do it manually it works fine, but using code it just won't work??


Michael

 

[Greg Truby]

I just inserted a photo into a worksheet, printed it, ran that code and printed again -- one image good, one image not so purdy -- so it worked just fine for me. 'fraid I dunno what ta tell ya. Maybe set a watch on activesheet.pagesetup.printquality and step through TogglePrintQuality to see what's going on?

 

[daniels012]

let me ask you this. I put the code here:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
        .PrintQuality = IIf(.PrintQuality(1) = 600, 300, 600)
    End With
End Sub

Would this have any effect on this? Or do I need it in a worksheet, module or somewhere else.

I am not sure how to do what you are suggesting here?

Maybe set a watch on activesheet.pagesetup.printquality and step through TogglePrintQuality to see what's going on?

Thank you for your help though!!
Michael

 

[Greg Truby]

Michael,

My code was just to show "how to" and furthermore, it's a toggle. So putting it in the beforeprint() event handler is going to make it flip back and forth. Furthermore putting it in the event handler means you ALWAYS want to print at xxx dpi. Since you are trying to do this in code instead of just doing it by hand, I assume that there are times where you want code to print @ 300 dpi and times where you want your code to print @ 600 dpi. Otherwise just set by hand the one time and get on down da road, yes?

So all you should need is

ActiveSheet.PageSetup.PrintQuality = 300

or

ActiveSheet.PageSetup.PrintQuality = 600

at points in your code where you want to print at 300 dpi & 600 dpi respectively.
Regarding my prior advice -- have you never set a watch in the VBE?

1. From the VBE menu Debug | Add Watch...
2. Enter activesheet.pagesetup.printquality as the watch expression
3. walk through the code using the F8 key
4. one the expression initializes, it is collapsed (since it's an array), expand the array and continue to step (F8) through the code monitoring the values of the array elements to see what's going on.