How to Set the Font Size of a Left Footer Using Excel VBA
Question details
The user needs to set a specific font size for a worksheet's left footer dynamically using Excel VBA.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating and customizing worksheet page layouts and footer formatting using VBA macros before printing or exporting.
- Observed behavior
- A custom font-size control code must be appended to the LeftFooter property to successfully format the text pulled from a named range.
Before running the VBA macro, ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have defined the necessary named ranges (such as 'UKaddress') in your worksheet.
Use a VBA Macro to Format Footer Font Size
Apply a font-size control code to the PageSetup.LeftFooter property using a dedicated VBA macro script.
In Excel VBA, you can format footer text by concatenating specific control codes directly into the footer string. Using the ampersand (&) followed by a number sets the font size for the text that follows it.
Press Alt + F11 on your keyboard to launch the Visual Basic for Applications (VBA) editor.
In the top menu, click on 'Insert' and select 'Module' to create a blank script window.
Copy and paste the following code into the module window: Sub Footer() Const FontSize = 8 ActiveSheet.PageSetup.LeftFooter = "&" & FontSize & Range("UKaddress").Value End Sub.
Close the VBA editor and run the macro to update the left footer of your active worksheet.
Automate Page Setup Using Macros in WPS Spreadsheet
WPS Spreadsheet fully supports VBA macros, enabling you to automate page layouts, including custom footers and headers. You can run your existing Excel macros seamlessly within a familiar, lightweight interface.
- 1. Open Your Workbook in WPS Spreadsheet: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
- 2. Access the Developer Tools: Navigate to the 'Developer' tab on the top ribbon menu.
- 3. Launch the VBA Editor: Click the 'Visual Basic' icon to open the integrated code editor.
- 4. Run Your Footer Macro: Insert a new module, paste your footer formatting code, and click 'Run' to instantly update your worksheet.
Frequently Asked Questions
How do I apply the font size to the Right Footer in VBA?
You can use the exact same pattern by targeting the RightFooter property. For example, use the code: ActiveSheet.PageSetup.RightFooter = "&" & FontSize & Range("UKaddress2").Value.
Why does my VBA code fail when setting both left and right footers simultaneously?
This usually happens if the formatting control code (like "&8") isn't explicitly concatenated for each footer property separately. You must apply the size control code individually to LeftFooter, CenterFooter, and RightFooter rather than trying to define them all in a single bulk operation.
Can I change the font style to bold or italic using VBA footers?
Yes, you can include additional control codes in the string. For example, using "&B" before the text will make the footer bold, and "&I" will make it italic. You can string these together with your font size code.
