Excel VBA, Font Name

In this article I will explain how you can change the font (name) used in a cell or range. I’ve also provided a sample code which gets the current font (name) used in a cell or range.

You can download the code and file used in this article here.

Jump To:


Basics:

Using the code below you can change the font (name) used in cell A1 to “Arial”:

Range("A1").Font.Name = "Arial"


Example1, Set Font:

In sheet 2 there is a list of available font styles:

Excel VBA, Fonts
In row 1 of sheet 1, drop down lists are created which the user is able to select a font name from:

Excel, VBA, Drop Down Lists, Sheet 1
Excel, VBA, Drop Down Lists, Sheet 1, Lucidia Handwriting
In the figures below the user selects the Vineta BT font for cell C2:

Excel VBA, Vineta BT Font
Excel, VBA, Drop Down Lists, Sheet 1, Vineta BT
The drop down lists are created using data validation. The values for the data validation are in sheet2:

Excel VBA, Fonts
For more information about creating drop down lists in Excel please see Excel VBA Drop Down Lists.

The program uses a Worksheet_Change event handler. The event handler executes when the user selects a new value from the drop down lists:

'executes when the user selects a value from
'the drop downn list
Private Sub Worksheet_Change(ByVal target As Range)
Dim i As Integer
'loops through the columns in sheet1
For i = 1 To 5
'changes the font of the cell in row 2 to the font
'selects in row 1
Range(Cells(2, i), Cells(2, i)).Font.Name = Cells(1, i)
Next i
End Sub

The code below loops through the five columns:

For i = 1 To 4
...
Next i

The line below changes the font of the cell in row 2 to the font (name) selected in row 1:

Range(Cells(2, i), Cells(2, i)).Font.Name = Cells(1, i)


Example 2, Get Font:

In this example there is text written in row 2. When the user presses the Run button, in row 1 the font (name) used in the cell below ( row 2) is printed:

Excel VBA, Get Font Names

By changing the font (name) for the cells in row 2 and pressing the run button the values in row 1 are updated:

Excel VBA, Get Font Names Changes
The program uses a Button_Click event handler. The event handler executes when the user presses the Run button:

'executes when the user clicks the Run button
Private Sub btnRun_Click()
Dim i As Integer
'loops through the three columns
For i = 1 To 3
'prints the font name in row 1
Cells(1, i) = Range(Cells(2, i), _
Cells(2, i)).Font.Name
Next i
End Sub

The code below loops through the three columns:

For i = 1 To 3
...
Next i

The code below prints the name of the font used in row 2 in row 1:

Cells(1, i) = Range(Cells(2, i), _
Cells(2, i)).Font.Name

You can download the code and file used in this article here.

See also:

 

If you need assistance with your code, or you are looking for a VBA programmer to hire feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *