Forum Discussion

Deleted's avatar
Deleted
Aug 21, 2018

Headers and footers - can I include a cell reference?

Hi! I'm creating a spreadsheet for my users to treat as template. I want the detail that they enter in cell C5 to be included in the footer. I know the standard options let me include a worksheet label, but that's not the content I want. Does anyone know if it's possible to do this? If so, what's the syntax?

  • Hi,

     

    This is not possible unless you inject the worksheet with some lines of code!

    So please hover the mouse over the worksheet tab, right-click, and select View Code.

     

    Then copy the below code and paste it into the worksheet code module.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("C5")) Is Nothing Then
            PageSetup.CenterFooter = Range("C5").Value
        End If
    End Sub

     

    Next, save the workbook as .xlsm file extension in order to retain the code in the workbook.

     

    I hope that helps

    Haytham

  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor

    Hi,

     

    This is not possible unless you inject the worksheet with some lines of code!

    So please hover the mouse over the worksheet tab, right-click, and select View Code.

     

    Then copy the below code and paste it into the worksheet code module.

    Private Sub Worksheet_Change(ByVal Target As Range)
        If Not Intersect(Target, Range("C5")) Is Nothing Then
            PageSetup.CenterFooter = Range("C5").Value
        End If
    End Sub

     

    Next, save the workbook as .xlsm file extension in order to retain the code in the workbook.

     

    I hope that helps

    Haytham

    • Charmaine_Yee's avatar
      Charmaine_Yee
      Copper Contributor

      Haytham Amairah 

       

      Hello thanks for the code!

      I have tried to make it as left footer by changing Line 3 to PageSetup.LeftFooter instead of PageSetup.CentreFooter but it did not work, can you advise how can i get the footer information on the left instead of centre? 

    • Deleted's avatar
      Deleted
      Hi, Haytham.
      Thank you very much for the code! Sadly, for the purposes of this particular workbook, the delivery mechanism will “spit a dummy” if I use an .xlsm. It’s very useful to have the code for use in other contexts, though.
      I appreciate your time.
      Truis

Resources