Forum Discussion
Deleted
Aug 21, 2018Headers 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 SubNext, save the workbook as .xlsm file extension in order to retain the code in the workbook.
I hope that helps
Haytham
- Haytham AmairahSilver 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 SubNext, save the workbook as .xlsm file extension in order to retain the code in the workbook.
I hope that helps
Haytham
- Charmaine_YeeCopper Contributor
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?
- DeletedHi, 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