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
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
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.
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
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.
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 c00Sub getGoing()
c00 = "no font selected"
frmSelectFont.Show
MsgBox c00
End Sub