Tom’s Tutorials For Excel: Listing and Verifying Fonts

Tom’s Tutorials For Excel: Listing and Verifying Fonts

While you can press Alt+O+E to call the Format Cells dialog box and see what fonts are available, with the below macro named ListFonts you can see your fonts on a worksheet all at once, formatted as their font names. The other macro named VerifyFont helps you determine if the font name you’re inquiring about is loaded onto your system’s Excel application.



The two “VerifyFont” macros are identical except that the second one tells you which cell in the list holds the font name you are searching. Note the (0, 0) notation in the code which removes the absolute $ symbols from the cell address, helping the message be a little more reader-friendly.

Option Compare Text

Sub ListFonts()
Application.ScreenUpdating = False
Dim cbc As CommandBarControl, i%
Set cbc = Application.CommandBars.FindControl(ID:=1728)
For i = 1 To cbc.ListCount
With Cells(i, 1)
.Value = cbc.List(i)
.Font.Name = cbc.List(i)
End With
Next i
Columns(1).AutoFit
Set cbc = Nothing
Application.ScreenUpdating = True
End Sub


Private Sub VerifyFont() Dim myFont$ myFont = InputBox("Enter font name:", "Font type installed?") If myFont = "" Then Exit Sub Dim cbc As CommandBarControl, i% Set cbc = Application.CommandBars.FindControl(ID:=1728) For i = 1 To cbc.ListCount If myFont = cbc.List(i) Then MsgBox "Yes, the font ''" & myFont & "'' is installed.", 64, "Verified." Exit Sub Exit For End If Next i MsgBox "No, the font ''" & myFont & "'' is NOT installed.", , "Missing." End Sub

Sub VerifyFontListed() Dim myFont$ myFont = InputBox("Enter font name:", "Font type installed?") If myFont = "" Then Exit Sub Dim cbc As CommandBarControl, i% Set cbc = Application.CommandBars.FindControl(ID:=1728) For i = 1 To cbc.ListCount If myFont = cbc.List(i) Then MsgBox "Yes, the font ''" & myFont & "'' is installed." & vbCrLf & _ "See it in cell " & Cells(i, 1).Address(0, 0) & ".", 64, "Verified." Exit Sub Exit For End If Next i MsgBox "No, the font ''" & myFont & "'' is NOT installed.", , "Missing." End Sub
Share Button
Posted in Tom's Tutorials for Excel
Tags: , , , , , , , , , , , ,
3 comments on “Tom’s Tutorials For Excel: Listing and Verifying Fonts
  1. Jeff says:

    Very handy, thanks for sharing. Do you know how to find out what sizes are available for the non-true type fonts?

    • Tom Urtis says:

      I’d think that any font size is available up to 409. The drop down list of font sizes is only a convenience. You can type in whatever font size you like (up to 409), and that includes decimalized numbers or whole numbers that do not appear in the drop down list. There may be a limitation due to your system or the screen size where a large font size is not practical, but otherwise font size is a fairly flexible property.

  2. Julian says:

    Can it be possible to choose the font type directly from the list using hyperlink or something else other than from the Format Cells dialog box?

Leave a Reply to Jeff Cancel reply

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

*