asked on

MS Excel VBA to assign Font Name and Size

Experts,

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

errhandler:
i = i + 1
If i < 5 Then
Resume
Else
Resume Next
End If

End Sub

ASKER CERTIFIED SOLUTION

AccessGuy1763

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

unknown_routine

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
errhandler.

put breakpoint on errhandler: and also .Size = 12 to see if code stops there.

CFMI

ASKER

Fabulous, the code worked well - Thank you!