In an userform list all available fonts

The motivation for this tip was to share how to

1) dynamically add controls to a userform
2) respond to events for these controls, and
3) specifically respond to events using a callback procedure that is located in another class module!

Since this may come across as a fairly technical topic, this tip utilizes the above capabilities to provide a functional solution:

1) list in an userform the names of all available fonts with each name shown using that font,
2) hover over the option button associated with a font to see a sample of every English keyboard character in that font,
3) click on the option button to select the font, and, finally,
4) use this capability to programmatically get the user’s selection, if any.

Below is an example of the font selector in action. Each OptionButton shows the name of one available font using the font itself. At the same time, the control tool tip shows the font name in English (see the Wide Latin tip). A sample of how every keyboard character will look in that font appears below the font selector frame.

The motivation for this example was a Daily Dose of Excel blog post by Michael (http://www.dailydoseofexcel.com/archives/2012/03/14/getting-a-font-list-to-a-combo-box-2/). He used a combo box to list the fonts available to Excel leveraging a technique shown in a tip by John Walkenbach (http://www.j-walk.com/ss/excel/tips/tip79.htm).

For a version in a page by itself (i.e., not in a scrollable iframe as below) visit http://www.tushar-mehta.com/publish_train/xl_vba_cases/1054%20show%20fonts%20in%20userform.shtml

Tushar Mehta

5 thoughts on “In an userform list all available fonts

  1. Hi Tushar –

    Neat stuff! When I did my version, I found that a couple of ornamental fonts would not show up in their font face, namely Symbol and Zapf Dingbats. Did you have the same problem? I’m stealing your tool tip idea.

    … mrt

  2. Hi Michael: Thanks. All the fonts that I tested, including Symbol worked as expected. At the same time, I do not have Zapf Dingbats on my machine.

  3. Hi Tushar –

    Thanks. Must be a Mac thing then. I couldn’t and still can’t figure it out.

    Both you and John W used this indexing adjustment:

    ReDim Arr(FontList.ListCount - 1)
    For I = 0 To UBound(Arr)
    Arr(I) = FontList.List(I + 1)
    Next I

    and I just went from i = 1 to FontList.listcount. Is this just programmer's preference, or is there something more going on that I don't get?

    … mrt

  4. I didn’t realize John used the same indexing approach.

    By default, VBA uses zero-based arrays whereas Excel and Office methods / properties return 1-based collections. I used to program VBA with 1-based arrays but since .Net enforces zero-based arrays, it was just as easy for me to consistently use zero-based arrays.

  5. I prefer making optionbuttons visible/invisible instead of creating them during macro execution.
    I also think there’s som simpler code to make an inventory of foantnames:


    Public AllOptions As New Collection

    Private Sub UserForm_Initialize()
    With Application.CommandBars.Add.Controls.Add(, 1728)
    For j = 0 To .ListCount - 1
    c01 = c01 & "|" & .List(j + 1)
    Next
    .Parent.Delete
    End With

    sn = Split(Mid(c01, 2), "|")
    For j = 0 To 89
    With Me("optionbutton" & j + 1)
    .Visible = j < = UBound(sn) If j <= UBound(sn) Then .Caption = sn(j) .Font.Name = sn(j) AllOptions.Add New clsFormObj, "font" & j Set AllOptions("font" & j).xFormItem = Me("optionbutton" & j + 1) End If End With Next End Sub

    I also think the class module can be reduced to:
    Public WithEvents xFormItem As MSForms.OptionButton

    Private Sub xFormItem_Click()
    c00 = xFormItem.Caption
    End Sub

    Private Sub xFormItem_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    With xFormItem
    c02 = .Caption
    .ControlTipText = c02
    With .Parent.Parent
    .lblSample.Font.Name = c02
    .lblFontName.Caption = c02
    End With
    End With
    End Sub

    the same applies to the starting module:

    Public c00

    Sub getGoing()
    c00 = "no font selected"
    frmSelectFont.Show
    MsgBox c00
    End Sub

Leave a Reply

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