SOLVED

Headers and footers - can I include a cell reference?

Deleted
Not applicable

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?

3 Replies
best response
Solution

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

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

@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? 

1 best response

Accepted Solutions
best response
Solution

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

View solution in original post