Change font name for cells in column

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Change font name for cells in column

Post by YasserKhalil »

Hello everyone
I have a code that lists all the fonts installed and I tried to adopt the code to change the font of the cells in column B but it throws error (Unable to seet the name property of the Font class)

Code: Select all

Sub Test()
    Dim oWord As Object, i As Long
    On Error GoTo Error_Handler
    Set oWord = CreateObject("Word.Application")
    
    For i = 1 To oWord.FontNames.Count
        Cells(i + 1, 2).Font.Name = CStr(oWord.FontNames(i))
    Next i
    
Error_Handler_Exit:
    On Error Resume Next
    oWord.Quit
    Set oWord = Nothing
    Exit Sub
Error_Handler:
    MsgBox "The Following Error Has Occured." & vbCrLf & vbCrLf & _
        "Error Number: " & Err.Number & vbCrLf & _
        "Error Source: EnumerateFonts" & vbCrLf & _
        "Error Description: " & Err.Description, _
        vbCritical, "An Error Has Occured!"
    Resume Error_Handler_Exit
End Sub

User avatar
HansV
Administrator
Posts: 78237
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Change font name for cells in column

Post by HansV »

The code works for me...

S0720.png

Try to find out for which font name the code fails.
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Change font name for cells in column

Post by YasserKhalil »

Thanks a lot. I have restarted my pc and the code is working for most of the fonts correctly.

User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Change font name for cells in column

Post by SpeakEasy »

Quick question - why are you using Word to enumerate fonts in Excel given Excel has the ability to enumerate the fonts itself?

YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Change font name for cells in column

Post by YasserKhalil »

Thanks a lot. Can you show me how to do that in excel?

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Change font name for cells in column

Post by StuartR »

YasserKhalil wrote:
30 Aug 2021, 05:16
Thanks a lot. Can you show me how to do that in excel?

Code: Select all

Sub ListFontNames()
    Dim FontList
    Dim i As Long
        
    Set FontList = Application.CommandBars("Formatting").FindControl(ID:=1728)
    
    For i = 1 To FontList.ListCount
        Cells(i, 1) = FontList.List(i)
        Cells(i, 1).Font.Name = FontList.List(i)
        If i > 5000 Then Exit For
    Next i
End Sub
StuartR


User avatar
SpeakEasy
4StarLounger
Posts: 536
Joined: 27 Jun 2021, 10:46

Re: Change font name for cells in column

Post by SpeakEasy »

StuartR beat me to it. My solution is pretty much the same. Although I'd be tempted to replace

Dim FontList

with

Dim FontList As CommandBarControl
Last edited by SpeakEasy on 30 Aug 2021, 15:45, edited 1 time in total.

User avatar
StuartR
Administrator
Posts: 12577
Joined: 16 Jan 2010, 15:49
Location: London, Europe

Re: Change font name for cells in column

Post by StuartR »

SpeakEasy wrote:
30 Aug 2021, 12:02
StuartR beat me to it. My solution is pretty much the same. Although I'd be tempted top replace

Dim FontList

with

Dim FontList As CommandBarControl
I fully agree, but I couldn't remember what type of entity it was!
StuartR


YasserKhalil
PlatinumLounger
Posts: 4911
Joined: 31 Aug 2016, 09:02

Re: Change font name for cells in column

Post by YasserKhalil »

That's great. Thank you very much for your great solutions.