3000AD Knowledge Base
Home » Categories » Multiple Categories

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. 

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 


      

  TEST THE 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:

Always run the Macro Before Printing to PDFeXPLODE Printer

You can change LeftHeader to:
RightHeader   or CenterHeader   or LeftFooter or CenterFooter or RightFooter
 
You can change font color in this line :  HdrFtr(ws.Range("A1000").Value, "Arial,Regular", vbBlack, 7) 
vbBlack, vbBlue, vbWhite, VbRed
 
You can change font size from '7' to any size, for example '30'
 
The reference to cell A1000 can be changed to any other cell of your choice. Just modify this line :  HdrFtr(ws.Range("A1000").Value, "Arial,Regular", vbBlack, 7) 
 
If you are looking to create a PDF-eXPLODE Tag in an Excel header or Footer, please see How do I use PDF-eXPLODE with Microsoft Excel? 
 

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.

AddHeaderToAll_FromEachSheet

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   

Comments Comments
There are no comments for this article. Be the first to post a comment.
Name
Email
Security Code Security Code

Subscribe to Knowledge Base

Get notified when new articles are added to the knowledge base.