placing a reference to a cell in a print header

[Paul James]

I'm looking for a way to display the value of a cell in a print header using
some kind of formula without using Visual Basic code. If the cell that
contains the value I want to display is in a range named "MyRange" in a
worksheet named "Sheet1," is it possible to place some kind of formula in a
print header so that it will always display the current value in MyRange
whenever the page is pinted out?

Thank you in advance.

Paul

[Frank Kabel]

Hi Paul

AFAIK there is no way to insert a formula into the header/footer
definition. You have to use the workbook event Before_print to insert
cell values into the header. e.g.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Text
End Sub

Put this into the workbook code module. The cell value of A1 will be
inserted.
HTH
Frank

[Paul James]

Thanks for your response, Frank.

I've been trying to use the Workbook_BeforePrint event in the way you
described, but I've had the problem that it doesn't usually update the page
header every time I print. For some reason, I have to go into Print Preview
before it updates the page header. My workaround has been to call a
separate Sub procedure from the code that prints the page. I call that Sub
right before the line that prints the page, and that sub sets the header
with ActiveSheet.PageSetup. . . . etc.

I don't know why my Workbook_BeforePrint event won't reset the page header
until I open Print Preview, either manually or with VBA, but I find I need
Print Preview to make it load the settings into the header.

I suppose I could live with the workaround I described above, but I thought
it might be simpler if you could put a cell reference directly in the
header. Like you, I haven't been able to figure out a way to get one to
work. It seems to interpret everything I type in there as a literal string.

Again, thank you for responding.

Paul

[Frank Kabel]

Hi Paul

> I've been trying to use the Workbook_BeforePrint event in the way you
> described, but I've had the problem that it doesn't usually update
> the page header every time I print.

[snip]
That's strange. I use this event quite often and have never encountered
any problems.

> I suppose I could live with the workaround I described above, but I
> thought it might be simpler if you could put a cell reference
> directly in the header. Like you, I haven't been able to figure out
> a way to get one to work. It seems to interpret everything I type in
> there as a literal string.

I think this is just one of the missing functionalty mysteries os MS
Excel :-) AFAIK there simply is NO way to use a cell reference in the
header/footer section. Maybe Microsoft will change this in a future
release.... ;-)

Frank

[Dave Peterson]

Maybe it's just that the activesheet wasn't the correct one in all your testing.

Instead of:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
ActiveSheet.PageSetup.CenterHeader = Range("A1").Text
End Sub

You could be more specific:

Private Sub Workbook_BeforePrint(Cancel As Boolean)

with worksheets("sheet1")
.PageSetup.CenterHeader = .Range("A1").Text
end with
End Sub

(Change "sheet1" to the correct name.)

--

Dave Peterson
ec3...@msn.com

[Paul James]

Thanks for your reply, Dave. In your message you suggested that my problem
might be that the activesheet wasn't the correct one. I thought of that,
but he way I dealt with it was to put an If test before the rest of the
code:

If ActiveSheet.Name = "dataEntry" Then . . .

So it would seem that can't be the problem.

Others have said they haven't enountered this problem, so I don't know why
it doesn't work in my application. I guess I'll just use that workaround I
described by calling a different sub to reset the page headers.

Paul

[edward...@gmail.com]

What if I don't want the "Active"Sheet, I want the whole workbook to print with todays date. And instead of using a cell as my reference, I want to just use my own formula. Just the "=" sign shows. I want to insert a formula so I think my syntax has to change. What do I put to let it know to use the following formula?

="Printed: " & TEXT(TODAY(),"mmm dd, yyyy.")