Link to home
Start Free TrialLog in
Avatar of Juan Velasquez
Juan VelasquezFlag for United States of America

asked on

How to change default settings of active printer via vba


I have written the following code to print out timesheets.  However, the following code is not overwriting the printer defaults.

 ws.PageSetup.PaperSize = xlPaperLegal
  ws.PageSetup.BlackAndWhite = True

I'm thinking that I need the access the active printer and change the defaults there.
The activeprinter object does not seem to provide a way to access the above settings.  I've looked at the printout arguments, but there doesn't seem to be a way to change the paper size and black and white settings there.

Private Sub cmdPrintCompletedTimesheets_Click()
    ' Comments:
    ' Params  :
    ' Modified:
    On Error GoTo PROC_ERR
    Dim wb As Workbook
    Dim ws As Worksheet
    Set wb = ThisWorkbook
    For Each ws In wb.Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            Application.EnableEvents = False
            ws.Range("C:C").EntireColumn.Hidden = False
            ws.Range("F:F").EntireColumn.Hidden = False
            ws.Range("I:I").EntireColumn.Hidden = False
            ws.Range("L:L").EntireColumn.Hidden = False
            ws.Range("O:O").EntireColumn.Hidden = False
            ws.Range("R:R").EntireColumn.Hidden = False
            ws.Range("U:U").EntireColumn.Hidden = False
            ws.Range("V:V").EntireColumn.Hidden = False
            ws.Range("W:W").EntireColumn.Hidden = False
            ws.Range("Z:Z").EntireColumn.Hidden = False
            ws.Range("AA:AA").EntireColumn.Hidden = False
            ws.Range("AB:AB").EntireColumn.Hidden = False
            ws.Range("AE:AE").EntireColumn.Hidden = False
            gblCheckBoxCalledFromCode = True
            wb.Worksheets(ws.Name).chkShowJobId.value = True
            gblCheckBoxCalledFromCode = True
            wb.Worksheets(ws.Name).chkShowCostCenter.value = True
            gblCheckBoxCalledFromCode = True
            wb.Worksheets(ws.Name).chkShowCostType.value = True
            gblCheckBoxCalledFromCode = True
            wb.Worksheets(ws.Name).chkShowDoubleTime.value = True
            gblCheckBoxCalledFromCode = True
            wb.Worksheets(ws.Name).chkShowWorkOrder.value = True
            gblCheckBoxCalledFromCode = True
            wb.Worksheets(ws.Name).chkShowLocation.value = True
            ws.PageSetup.PaperSize = xlPaperLegal
            ws.PageSetup.BlackAndWhite = True
        End If
    Application.EnableEvents = True
    Exit Sub
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, "Sheet1.cmdPrintCompletedTimesheets_Click"
    Resume PROC_EXIT
End Sub

Open in new window

Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Well when you use:

it will open the dialog box with all the printers and you have a button there Setup to choose for a specific printer what you want in term of paper etc... if you click on any printer it become the Activeprinter

so that this instruction:

After the previous one will return the printer you have selected. and this instruction:

ws.PrintOut will execute on the Activeprinter.

Avatar of Juan Velasquez


Hello gowflow,

I've reran the previous code and now it is working.  Go figure.
ahhaaha one of those ... GOD KNOWS WHY !!! maybe when you first ran it it was selecting an other printer and somehow you did not notice so now maybe you paid attention and pressed on Setup that recorded the change ???

You know better.
Avatar of Jacques Geday
Jacques Geday
Flag of Canada image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank, that was a good catch.