VBA & Macro Problems

How to Add Two Lines of Text to an Excel Footer Using VBA

Algirdas Jasaitis Oct 22, 2026 868 views

Question details

The user needs to insert two distinct lines of text—specifically a name followed by the date and time on the next line—into an Excel page footer using a VBA macro.

How to Add Two Lines of Text to an Excel Footer Using VBA

Product: Excel
Device & OS: not provided
Scenario: Automating document formatting for printing or exporting by adding customized, multi-line footer information via VBA.
Observed behavior: The goal is to successfully place a custom name on the first line and a dynamically generated date and time on the second line within the same footer section.

Before you start

Ensure that you have the Developer tab enabled in your ribbon and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) to prevent losing your VBA code.

Solution 1Recommended

Use the vbLf Constant to Insert a Line Break in the Footer

Combine both lines of text into a single VBA statement and separate them using the vbLf constant to force a line break.

When working with VBA's PageSetup properties, footers are treated as single continuous strings. To split the text into multiple lines, you must insert a line feed character (vbLf or Chr(10)) exactly where you want the line break to occur.

You can also include text formatting codes (like &12 for font size or &"Times New Roman" for font face) directly in the string.

Open the VBA Editor

Press Alt + F11 on your keyboard to open the Visual Basic for Applications (VBA) editor.

Insert a new module

Click on Insert in the top menu, then select Module to create a new blank script window.

Write the VBA code

Type your macro and use the vbLf constant between the first line (e.g., the name) and the second line (e.g., the date). For example: ActiveSheet.PageSetup.LeftFooter = "SUBMITTED BY: " & Title & wSurname & vbLf & "&12&""Times New Roman""" & Format(Now, "DD MMM YY hh:mm")

Run the macro

Press F5 to run your code or attach it to a worksheet button. Switch back to your worksheet and go to Print Preview to verify the two-line footer.

Use the vbLf Constant to Insert a Line Break in the Footer

Understanding Formatting Codes: In VBA footer strings, using double quotes inside the string (like ""Times New Roman"") correctly passes the font name to the page setup properties without breaking the code syntax.

Efficient Spreadsheet Management

Easily Manage Spreadsheets and Macros with WPS Office

WPS Spreadsheet offers robust support for VBA macros, allowing you to automate tasks like inserting multi-line footers effortlessly. It provides a familiar interface that is fully compatible with Microsoft Excel files.

1. Open your workbook: Launch WPS Spreadsheet and open your .xlsm or .xlsx file.
2. Access the Developer Tools: Navigate to the Developer tab on the ribbon to manage your macros.
3. Run your footer macro: Open the VBA editor, paste your footer code utilizing the vbLf line break, and run it to update your page setup.

Full compatibility with Microsoft Excel (.xlsx and .xlsm) formats.Advanced VBA and macro support for automated page setup.Lightweight application with fast launch speeds.Free to download and use with comprehensive spreadsheet features.

microsoft office alternative - wps office

Frequently Asked Questions

Can I use vbCrLf instead of vbLf for footer line breaks?

While vbCrLf (Carriage Return + Line Feed) works in many VBA message boxes and strings, header and footer properties specifically respond best to vbLf or Chr(10) to create a clean line break without adding extra hidden characters.

How do I format the date and time differently in the VBA footer?

You can change the Format(Now, "DD MMM YY hh:mm") portion of the code. For example, to show a standard US date, use Format(Now, "MM/DD/YYYY"). The Format function gives you complete control over how the date is displayed.

Why isn't my VBA macro saving when I close the workbook?

By default, standard workbooks (.xlsx) cannot save VBA macro code. You must save your document as a Macro-Enabled Workbook (.xlsm) or a Binary Workbook (.xlsb) to keep your macros intact.

How do I add a two-line footer to the center or right section instead?

Simply change the property in your VBA code from LeftFooter to CenterFooter or RightFooter. For example: ActiveSheet.PageSetup.CenterFooter = "Line 1" & vbLf & "Line 2".

Excel & Data Problems