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:
Contents
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:
In row 1 of sheet 1, drop down lists are created which the user is able to select a font name from:
In the figures below the user selects the Vineta BT font for cell C2:
The drop down lists are created using data validation. The values for the data validation are in sheet2:
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:
By changing the font (name) for the cells in row 2 and pressing the run button the values in row 1 are updated:
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