Скачать или смотреть How to Change the Font Format of a Footer Value in Excel Using VBA

Learn how to modify the font format of footer values in Excel with VBA by following this simple guide. Transform your reports with customized fonts!
---
This video is based on the question https://stackoverflow.com/q/62777259/ asked by the user 'smirnoff103' ( https://stackoverflow.com/u/1845269/ ) and on the answer https://stackoverflow.com/a/62778159/ provided by the user 'Dy.Lee' ( https://stackoverflow.com/u/8107092/ ) 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: Set text format in footer

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.
---
How to Change the Font Format of a Footer Value in Excel Using VBA

Adding custom values to the footer of your Excel documents can enhance the presentation of your reports, making them more professional. However, many users encounter difficulties when trying to format these footer values—such as changing the font style or size—using VBA. If you’ve experienced this challenge, you’re not alone.

In this guide, we’ll walk through a solution that allows you to seamlessly change the font format of footer values within your Excel documents via VBA. We’ll break this solution down into organized sections for clarity.

Understanding the Issue

When inserting footer content using VBA, it’s important to note that the footer settings are treated differently from typical cell formatting in Excel. This is likely the source of confusion when you attempt to set font colors or sizes directly in the footer setup.

The original code that some users attempt may look like this:

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

This seems straightforward, but it often results in the footer text appearing in the default font with no changes applied.

The Solution

To effectively change the footer text format, follow these steps:

Step 1: Rewrite the Code

Here’s an improved version of your VBA code that incorporates font formatting directly into the string you assign to the footer:

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

Step 2: Explanation of the Code

Get the Value: First, we retrieve the value from a specific cell (A1) in the active sheet. This is what will populate your footer.

Set the Footer Format:

&8 specifies the font size.

&kC0504D sets the font color (in HTML format).

By combining these codes, you format not only the text that displays but also its appearance.

Running the Code

To execute this code:

Open Excel and press ALT + F11 to access the VBA editor.

Insert a new module via Insert > Module.

Paste the rewritten code into the module window.

Close the VBA editor and return to Excel.

Run the macro via Developer > Macros, select SetValueInFooter, and hit Run.

Once executed, your footer should now reflect the proper font size and color as specified!

Conclusion

Formatting footer texts in Excel using VBA can be tricky at first, but by adapting your approach as shown above, you can achieve a polished look for your documents. Whether for reporting or presentation purposes, customizing footer text will help your spreadsheets stand out. Happy coding!