Tag: class

In an userform list all available fonts

Posted on by Tushar Mehta

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

Passing Arguments to a Userform

Posted on by Dick Kusleika

The Initialize event of userforms doesn’t take an argument, so you can’t pass data directly to it. Here are three ways to pass a variable to a userform.

Global Variable

I avoid using global variables whenever practical, but depending on the complexity of the application, it may be the best choice. Create a global variable in a standard module and set it before showing the form.

Then you can use that variable in the userform’s code

The Tag Property

If you’re not using the Tag property for something else, you can store some data in it for later use. You just need to load the form before you set the property, then show it.

Properties

Because a userform is just a class module with a built-in user interface, you can create properties in the form’s module. The form’s module might look like this

and showing the form from a standard module