We help IT Professionals succeed at work.
Get Started

How to change default settings of active printer via vba

978 Views
Last Modified: 2014-03-17
Hello,

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
    Application.Dialogs(xlDialogPrinterSetup).Show
    For Each ws In wb.Worksheets
        If Left(ws.Name, 3) = "WTS" Then
            Application.EnableEvents = False
            ws.Unprotect
            
            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
            ws.Protect
            ws.PrintOut
        End If
    Next
    
PROC_EXIT:
    Application.EnableEvents = True
    Exit Sub
    
PROC_ERR:
    MsgBox Err.Number & vbTab & Err.Description, vbCritical, "Sheet1.cmdPrintCompletedTimesheets_Click"
    Resume PROC_EXIT
    
End Sub

Open in new window

Comment
Watch Question
Partner
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 5 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE