UDF to return page headers/footers text

April 7, 2008 by JP ·

Here's a series of short UDFs that will return the page headers and footers for the active worksheet.

Function GetLeftHeader()
  GetLeftHeader = ActiveSheet.PageSetup.LeftHeader
End Function

Function GetCenterHeader()
  GetCenterHeader = ActiveSheet.PageSetup.CenterHeader
End Function

Function GetRightHeader()
  GetRightHeader = ActiveSheet.PageSetup.RightHeader
End Function

Function GetLeftFooter()
  GetLeftFooter = ActiveSheet.PageSetup.LeftFooter
End Function

Function GetCenterFooter()
  GetCenterFooter = ActiveSheet.PageSetup.CenterFooter
End Function

Function GetRightFooter()
  GetRightFooter = ActiveSheet.PageSetup.RightFooter
End Function

To use: Just put the function name in the cell, prefixed by an equal sign. For example, to get the left header text in cell A1:

=GetLeftHeader()

If you use any of these functions in a workbook other than the one where the code resides (ex: your PERSONAL.XLS workbook), you may have to prefix the code like this:

=PERSONAL.XLS!GetLeftHeader()

You can also use them in VBA code to get the associated property and take appropriate action, for example:

Sub TestMe()
Dim strMyHeader As String

strMyHeader = GetLeftHeader

If strMyHeader <> "My Company Name" Then
  ActiveSheet.PageSetup.LeftHeader = "Company Name"
Else
  MsgBox strMyHeader & " is your company's name."
End If

End Sub

HTH,
JP