Thanks Thanks:  0
Likes Likes:  0
Results 1 to 10 of 10

Thread: List all Font Names in a Range ...

  1. #1
    Guest

    Default

    Hi,

    I would like to list all available Fonts, but not the file name ("abac.ttf") but the Font name ("Abadi MT Condensed").
    In fact it's for applying the font to the next column.

    Range("A1").Font.Name = "abac.ttf" ==> runs but it doesn't work like :
    Range("A1").Font.Name = "Abadi MT Condensed"

    Thanks,
    Alx.

  2. #2
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    To get the fonts names try this


    Sub GetFonts()
    Dim Fonts
    Dim x As Integer

    Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Select '.ClearFormats
    x = 1
    Set Fonts = Application.CommandBars.FindControl(ID:=1728)

    On Error Resume Next
    Do Until Err <> 0
    Cells(x + 1, 1) = Fonts.List(x)
    x = x + 1
    Loop

    Range("A1").FormulaR1C1 = "=""Font List = "" & COUNTA(R[1]C:R[500]C)"

    With Range("A1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold"
    .Font.Size = 10
    .Font.Strikethrough = False
    .Font.Superscript = False
    .Font.ColorIndex = 5
    .Font.Interior.ColorIndex = 15
    End With
    Columns("A:A").EntireColumn.AutoFit

    Set Fonts = Nothing

    End Sub



    Ivan

    [ This Message was edited by: Ivan F Moala on 2002-07-21 23:22 ]

  3. #3
    New Member
    Join Date
    May 2003
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Excellent Ivan!

    I did a search and found this to be exactly what I was looking for.
    I appreciate it!

    Tom

  4. #4
    Board Regular
    Join Date
    Jul 2002
    Posts
    603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Set Fonts = Application.CommandBars.FindControl(ID:=1728)

    Ivan where from we get all the commandbar ID number? ID 1728 belongs to font name dropdown box control in the format command bar am I right?
    Regards
    GNaga

  5. #5
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Yes, thats correct....

    try something like this to get them...

    Kind Regards,
    Ivan F Moala From the City of Sails

  6. #6
    Board Regular
    Join Date
    Oct 2002
    Location
    Bergamo-Italy
    Posts
    627
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Quote Originally Posted by Ivan F Moala
    To get the fonts names try this


    Sub GetFonts()
    Dim Fonts
    Dim x As Integer

    Range(Cells(1, 1), Cells(1, 1).End(xlDown)).Select '.ClearFormats
    x = 1
    Set Fonts = Application.CommandBars.FindControl(ID:=1728)

    On Error Resume Next
    Do Until Err <> 0
    Cells(x + 1, 1) = Fonts.List(x)
    x = x + 1
    Loop

    Range("A1").FormulaR1C1 = "=""Font List = "" & COUNTA(R[1]C:R[500]C)"

    With Range("A1")
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Font.Name = "Arial"
    .Font.FontStyle = "Bold"
    .Font.Size = 10
    .Font.Strikethrough = False
    .Font.Superscript = False
    .Font.ColorIndex = 5
    .Font.Interior.ColorIndex = 15
    End With
    Columns("A:A").EntireColumn.AutoFit

    Set Fonts = Nothing

    End Sub



    Ivan

    [ This Message was edited by: Ivan F Moala on 2002-07-21 23:22 ]

    Hi Ivan,
    but how can I get a sample for each font too?

    Tia.
    Maurizio

  7. #7
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Loop through each cell and do something like

    Code:
    For Each Cll In Range("A2", Range("A65536").End(xlUp))
        Cll.Font.Name = Cll.Value
    Next Cll
    Regards,

    Juan Pablo González
    http://www.juanpg.com

  8. #8
    MrExcel MVP Ivan F Moala's Avatar
    Join Date
    Feb 2002
    Location
    Auckland, New Zealand
    Posts
    4,209
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Thanks Juan....I went away looking for the File
    Kind Regards,
    Ivan F Moala From the City of Sails

  9. #9
    Board Regular
    Join Date
    Oct 2002
    Location
    Bergamo-Italy
    Posts
    627
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Thanks Juan to me, also.
    Maurizio

  10. #10
    Board Regular
    Join Date
    Oct 2002
    Location
    Bergamo-Italy
    Posts
    627
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: List all Font Names in a Range ...

    Hi Juan Pablo,
    using:

    For Each Cll In Range("A2", Range("A65536").End(xlUp))
    Cll.Font.Name = Cll.Value
    Next Cll

    with excel 97 after 150 fonts I get "Insufficient memory" and then error 404, not with excel XP.
    Any solution for old excel 97?

    Tia.
    Maurizio

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •