Скачать или смотреть How to Populate a Combobox with Font Names in Excel VBA

Discover how to effectively populate a combobox with font names in Excel VBA to enhance your userform experience.
---
This video is based on the question https://stackoverflow.com/q/64093585/ asked by the user 'Tomas Perlecky' ( https://stackoverflow.com/u/12277781/ ) and on the answer https://stackoverflow.com/a/64100613/ provided by the user 'T.M.' ( https://stackoverflow.com/u/6460297/ ) at 'Stack Overflow' website. Thanks to these great users and Stackexchange community for their contributions.

Visit these links for original content and any more details, such as alternate solutions, latest updates/developments on topic, comments, revision history etc. For example, the original title of the Question was: Populate Combobox with font names

Also, Content (except music) licensed under CC BY-SA https://meta.stackexchange.com/help/l...
The original Question post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license, and the original Answer post is licensed under the 'CC BY-SA 4.0' ( https://creativecommons.org/licenses/... ) license.

If anything seems off to you, please feel free to write me at vlogize [AT] gmail [DOT] com.
---
Introduction

When working on userforms in Excel VBA, one common task is to populate a combo box with available font names installed on the user’s computer. This can significantly enhance user experience by providing a visually appealing selection tool. However, many developers encounter frustration when trying to achieve this, especially when faced with errors such as "Run-time error '-2147467259 (80004005)". In this guide, we’ll explore a solution to correctly populate a combobox with font names in Excel VBA.

The Problem at Hand

You may have written code to populate a combobox but run into errors or discover that the combobox remains empty even after running your initialization code. This can happen for various reasons, including:

The font control not being available in the command bars.

Issues with how you're accessing the list of fonts.

The handling of errors in the code causing data not to load as expected.

Let’s dig into a solution that addresses these issues effectively.

The Solution: Step-By-Step Guide

1. Basics of the Functionality

To begin, you will use a more efficient approach by creating a separate function GetFontList() that retrieves the list of available fonts and returns them in a format that the combobox can understand.

2. The Modified Code

Here's the complete VBA code necessary for this task:

UserForm Initialization

[[See Video to Reveal this Text or Code Snippet]]

Function to Retrieve Font Names

[[See Video to Reveal this Text or Code Snippet]]

3. Key Points to Remember

Error Handling: The use of On Error Resume Next helps avoid abrupt stops in your code when a control cannot be found.

Temporary CommandBars: If necessary, you can create a temporary command bar just to access the font list. However, ensure you delete it afterward to clean up.

Array Usage: The combobox’s List property can accept a one-based array, making it straightforward to populate the items.

Conclusion

By following the steps outlined above, you can successfully populate a combobox with font names in your Excel VBA userforms. This not only enriches the functionality of your applications but also provides a smoother and more user-friendly interface.

Implement this solution in your projects and enhance how your users interact with font choices in Excel!