MS Excel VBA to assign Font Name and Size


I am in the process of setting the workbook Font to Arial and size to 12 and would prefer to print to a single page.

When I call the following code, the cells in the workbook end with Arial and 10.

How can the code be modified or is there another solution?

Sub McrLayout(ws As Worksheet)
' File layout orientation
' Keyboard Shortcut: Ctrl+Shift+L
    Dim i As Long
    Application.PrintCommunication = False
    With ws.PageSetup
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(0.5)
        .BottomMargin = Application.InchesToPoints(0.5)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .Orientation = xlLandscape
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PaperSize = xlPaperLegal
    End With
    With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
    End With

    On Error GoTo errhandler
    Application.PrintCommunication = True
    On Error GoTo 0
    Exit Sub
    i = i + 1
    If i < 5 Then
        Resume Next
    End If

End Sub
CFMIFinancial Systems AnalystAsked:
Who is Participating?
AccessGuy1763Connect With a Mentor Commented:

I would give "With Sheet.Cells.Font" (in your case "With ws.Cells.Font") a try in place of "With Selection.Font"

Here is my reference:
This code:

With Selection.Font
        .Name = "Arial"
        .FontStyle = "Bold"
        .Size = 12
 End With

Should always work (if some cells are selected on worksheet).

The only scenario it may not work is when an error happens and code jump to

put breakpoint on errhandler: and also   .Size = 12 to see if code stops there.
CFMIFinancial Systems AnalystAuthor Commented:
Fabulous, the code worked well - Thank you!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.