You are here: Home > Excel > UDF to return page headers/footers text

UDF to return page headers/footers text


April 7, 2008 – 4:07 pm by JP

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

plain text version
VBA:
  1. Function GetLeftHeader()
  2.   GetLeftHeader = ActiveSheet.PageSetup.LeftHeader
  3. End Function

plain text version
VBA:
  1. Function GetCenterHeader()
  2.   GetCenterHeader = ActiveSheet.PageSetup.CenterHeader
  3. End Function

plain text version
VBA:
  1. Function GetRightHeader()
  2.   GetRightHeader = ActiveSheet.PageSetup.RightHeader
  3. End Function

plain text version
VBA:
  1. Function GetLeftFooter()
  2.   GetLeftFooter = ActiveSheet.PageSetup.LeftFooter
  3. End Function

plain text version
VBA:
  1. Function GetCenterFooter()
  2.   GetCenterFooter = ActiveSheet.PageSetup.CenterFooter
  3. End Function

plain text version
VBA:
  1. Function GetRightFooter()
  2.   GetRightFooter = ActiveSheet.PageSetup.RightFooter
  3. 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:

plain text version
VBA:
  1. Sub TestMe()
  2. Dim strMyHeader As String
  3.  
  4. strMyHeader = GetLeftHeader
  5.  
  6. If strMyHeader <> "My Company Name" Then
  7.   ActiveSheet.PageSetup.LeftHeader = "Company Name"
  8. Else
  9.   MsgBox strMyHeader & " is your company's name."
  10. End If
  11.  
  12. End Sub

HTH,
JP


Share and Enjoy:
  • StumbleUpon
  • Technorati
  • Digg
  • Google
  • del.icio.us
  • MisterWong

Print This Post Print This Post  |  Email This Post Email This Post  |  Permalink  |  Subscribe to this feed Subscribe now!

Filed Under: Excel, UDF, VBA
Tags: , , , ,

This post has 166 views since April 7, 2008 – 4:07 pm.

Post a Comment

To post VBA code in your comment, use [VBA] tags, like this: [VBA]Code goes here[/VBA].





Subscribe without commenting

Keep Reading:

Browse Posts:


« Rows to Repeat at Top || Updated blog and new pages »

               Copyright © 2007,2008 Code For Excel And Outlook || Disclaimer