How can I update an Excel Header or Footer with a Cell Value?
ISSUE:
MS Excel does not appear to be able to interpret a cell value inside any Page Header or Footer. Is there some sort of intelligent or automated way to update the Header or Footer with the value of the cell shown in the header?
ANSWER:
Yes there is, albeit it is only possible via VBA (Visual Basic for Applications) code in Excel Also once that code is placed inside the Workbook, it can be made available for each Worksheet. However the XLSX must then be saved as an Excel Macro-Enabled-Workbook (*.xlsm). This is how you can achieve this.
SET UP VBA CODE:
1. In Excel press Alt + F11 to enter the VBE interface.
2. Press Ctrl + R to show the Project Explorer if the Project window does not display
3. Click '+' to open the menu Microsoft Excel Objects. This should open and list each worksheet under this Object option plus an additional object named ThisWorkbook
4. Right-click ThisWorkbook to open a Module screen on which you will paste the VBA code for macro execution.
5. Choose Insert -> Module. >
6. Paste code from the 'yellow' window below, into the right Module pane.
SOURCE CODE:
Copy the code below from " Option Explicit " to " End Function " and paste it into your Module window. Make changes based on the notes/comments (in green) provided below.
Option Explicit
Sub AddHeaderToAll_FromEachSheet()
' Add a calculated cell (A1000) on each sheet in this workbook. When you run the MACRO "AddHeaderToAll_FromEachSheet",
' the code below will loop through each worksheet and update the Custom Header - Left Pane on that worksheet with the value
' of A1000 for that worksheet
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
' for Header and footer font color - use: vbWhite - WHITE, vbBlack - BLACK, vbRed - RED, vbBlue - BLUE
' 7 = font size
ws.PageSetup.LeftHeader = HdrFtr(ws.Range("A1000").Value, "Arial,Regular", vbBlack, 7)
Next ws
End Sub
Function HdrFtr(sText As String, Optional ByVal sFont As String, Optional iColor As Long, Optional iFontSize As Long) As String
Dim sColor As String
Dim sFontSize As String
If Len(sFont) Then sFont = "&""" & sFont & """"
If Abs(iFontSize) Then sFontSize = "&" & Abs(iFontSize)
If iColor <> 0 Then
sColor = "&K" & _
Right("0" & Hex(iColor And &HFF), 2) & _
Right("0" & Hex(iColor \ &H100 And &HFF), 2) & _
Right("0" & Hex(iColor \ &H10000 And &HFF), 2)
End If
HdrFtr = sFont & sFontSize & sColor & sText
End Function
7. Your screen is shown below (after the 'paste' is complete).
8. Click the SAVE icon on the toolbar and then press Alt + Q to close the VBA interface.
9. Click menu File/Save As to save the Workbook before any other changes to an Excel-Macro-Enabled Workbook (*.xlsm) so it will preserve the VBA code
1. Enter some calculated data in cell A1000 (on each sheet)
2. Press Alt + F8 to display the Macro dialog box.
3. Choose the macro AddHeaderToAll_FromEachSheet and click Run.
4. Once it has run, go to Page Setup|Print Titles and select Header/Footer tab and verify by clicking the Custom Header ,that your header has been set correctly.
5. To note, the code is set to show the color of the font as vbBlack. Change this to vbWhite to make the font white
NOTE 1:
Always run the Macro Before Printing to PDFeXPLODE Printer
Select the appropriate option as above.
Comments
You Can...
Subscribe to Article
Print Article
Email Article to Friend
Export to PDF
Export to MS Word
Article Information
Article Rating
Attachments
