Excel footer reference to cell in workbook

M

mlh97

I'm trying to have my Excel footer reference to a cell in the worksheet. Can
this be done? I saw where someone previously suggested something with VBE,
but 1) I know zero about VBE and 2) I tried it anyway and couldn't get it to
work.

Thanks.
 
T

Tom Ogilvy

You can't do it with any setting provided by excel.

You can use the BeforePrint event to execute code that updates the footer
with information from the cell. This code would need to go in the
ThisWorkbook module.

To get to that you go to the vbe (Alt+F11)

then in the project explorer window you will see your workbook listed.
underneath it look for an entry for

ThisWorkbook

double click on that

At the top of the resulting module
In the left dropdown, select Workbook
In the right dropdown select BeforePrint

this will place an Event subroutine declaration in the module

Private Sub Workbook_BeforePrint(Cancel As Boolean)

End Sub

Within this routine you can place the code to update the footer

Private Sub Workbook_BeforePrint(Cancel As Boolean)
for each sh in Activewindow.SelectedSheets
if sh.Name = "Sheet3" then
sh.PageSetup.LeftFooter = Worksheets("Sheet3") _
.Range("B9").Text
Exit for
end if
Next
End Sub
 
Top