CODEWITHSUNDEEP
excelvba
user14484553
user14484553

Reputation:

How do you get the different ThemeFonts used in a text in excel?

In Excel, I have a text in a cell that has two different fonts. One of them is variable, be it arial or calibri, and the other is Symbol. I need to be able to detect the symbol font and what is within it. I've tried to check for characters with values above 122, but these are just regular text values with a different font.

I tried using ThemeFont, such as

Dim WhatIsFont As ThemeFont
WhatIsFont = Worksheets("MySheet").Range("A1").Font.ThemeFont

But it returns Nothing when I this variable. And if I use WhatIsFont as a String, it instead gives me a numeric value between 0 and 2 based on what kind of fonts and how many are used. I cannot, for the life of me, find any other function that deals with ThemeFonts and know which ones exactly are being used.

Anyone knows how to do this? Thank you.

Upvotes: 0

Views: 94

Answers (1)

user14484553
user14484553

Reputation:

After a while of playing around, I found an answer to my own question through the following code:

Sub TestFont()
'
' TestFont Macro
'

'
Dim i As Long
Dim r As Range
ThisWorkbook.Worksheets("Instructions").Range("C11").Select

    For Each r In Selection
        For i = 1 To Len(r.Value)
            With r.Characters(i, 1).Font
                MsgBox .Name
            End With
        Next i
    Next r
End Sub

To answer my question then. ThemeFont won't cut it. Its .Font.Name that gives you the actual font being used here. By cycling through the several characters in a string - rather than investigate the string as a whole - you can pinpoint the Font name of each character, and thus from there single out the ones written in Symbol!

Upvotes: 1

Related Questions