Help w/vba to open dialog box and Select COLOR printer from list & change setup

[HunterN]

Hi,

I am using the following code to open the PrinterSetup Dialog box and select the color printer (which is not my default printer). Once the setup window appears I want to be able to unclick the 'Print in Grayscale' box, to make sure it prints in color. But using the following code does not show the Color tab for the color printer when I select it from the dialog box.

Here is the code I got from the 'EXCEL FORUM' website. It brings up the dialog box fine. I click the printer I want and then click the setup button, but it's like it doesn't recognize that I have changed the printer.

Function PrintWithColorCheck()
    Dim i As Long
    
    Worksheets("CPS8").Select
    
    Application.Dialogs(xlDialogPrinterSetup).Show
    
    With ActiveSheet
        .PageSetup.BlackAndWhite = _
        MsgBox("Do you want to print in color?", vbYesNo, _
                "Print out") = vbNo
            
        .PrintOut copies:=1
        
        '* Tidy up
        .PageSetup.BlackAndWhite = False
        
    End With
End Function

Even answering the message box when it asks 'Do you want to print in color' doesn't seem to help.

My default printer is not the color printer. The user's printer is a color printer and they want this report to come out in color.

Thanks for any thoughts.

Nancy

 

[NewOrderFac33]

Hi, Nancy,

Could you not find the full address of your user's printer and code it into your application thus:

Application.ActivePrinter = "\\hlsnps04p\Uttoxeter on Ne01:"

Pete

 

[NewOrderFac33]

...then build it into something like this:

Sub SelectPrinter()
    Dim Message, Title, Default, MyValue
    Message = "Colour or B&W Printing?"
    Title = "Do you want to print in colour?"
    Default = "Y"
    MyValue = InputBox(Message, Title, Default, 100, 100)
    Select Case UCase(MyValue)
        Case Is = "Y"
            ActiveSheet.PageSetup.BlackAndWhite = False
            'Specify your Colour printer here
            Application.ActivePrinter = "\\hlsnps04p\Uttoxeter on Ne01:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1
        Case Is = "N"
            ActiveSheet.PageSetup.BlackAndWhite = True
            'Specify your Black and White printer here
            Application.ActivePrinter = "\\hlsnps04p\Uttoxeter on Ne01:"
            ActiveWindow.SelectedSheets.PrintOut Copies:=1
        Case Else
            Exit Sub
    End Select
End Sub

 

[HunterN]

Thanks Pete,

I was trying not to use any hard coding of the printers name. But if I have to, I'll try out your 'Select Case' scenario.
Thanks for your input. I'll let you know how it works out.

Nancy