Cell contents in header/footer

[mdavison]

I'm sorry but I can't find the original thread, so I am posting to both
groups.

Someone asked how to display the contents of a cell in the header or footer
and someone else gave the code. works great! Thanks.
One issue: I am trying to use this in an overtime form, saved as an XLS in
Read Only format in MailSite v.8.0 SP1. Can't use it as an XLT in our
Novell/Win XP/Office 03/Interwoven environment.

I have users entering their employee ID number, which looks up their
concatenated name & department for display. But it also adds a special
department in a field which is formatted with white text so users wont freak
out and THAT is where the code in my header is supposed to pull the
reference from. IE: The cell contents are volatile. But now, every single
user's printout show MY department code in the header instead of the one
that matches their own lookup.
Is there some sort of fresh code that I need?

Please help! Thanks,
-Monica

 

[JLatham]

It would help if you posted the formula used to get the information. I
suspect that the formula contains a specific reference to the workbook you
used to set it up initially. That reference needs to be taken out of the
formula (if I'm right) so that it doesn't try to reference or link to your
workbook.

Check and see if you have links in any of the workbooks you created for
others: Edit | Links - and if this is the case, you'll see reference to link
to (your original) workbook.

 

[mdavison]

Its not a formula - its code. And I think I did it correctly the way the
original respondent posted it. Right?

'ThisWorkbook
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
..LeftHeader = Range("G3")
..CenterHeader = ""
..RightHeader = ""
End With
End Sub

 

[JLatham]

Since the default value of a range is its .Value you should be getting what
is displayed in G3 at the time. Probably need to either call someone up
that's having the issue and asking them what's showing in G3 on the sheet
while they have the file open - or walk down the hall and peek over their
shoulder to see for yourself.

To be more specific you could change the .LeftHeader = Range("G3") statement
to either
..LeftHeader = Range("G3").Value
or
..LeftHeader = Range("G3").Text

although in theory all three should give you pretty much the same results in
this case.

 

[mdavison]

Here is the formula used to look up the data - it works perfectly well.

=IF($B$3=0,0,LOOKUP($B$3,Sheet2!$A$2:$A$175,Sheet2!$G2:G$175))

Hereis the code I got from a previous post to add the above cell contents
into the left header. Its also works - but not 100% of the time.

'ThisWorkbook
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
..LeftHeader = Range("G3")
..CenterHeader = ""
..RightHeader = ""
End With
End Sub

Thanks all.

 

[mdavison]

I posted the formula and the Macro code. No links are involved. We use
WorkSite which makes linking from one file to another a royal pain anyway.
So I was pretty sure it was not an issue, but I DID check to be sure.
Typically, my spreadsheets are self-contained.
Thanks.