placing a reference to a cell in a print header

  • Thread starter Thread starter Paul James
  • Start date Start date
P

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
 
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
 
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
 
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.
Click to expand...
[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.
Click to expand...

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
 
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.)
 
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
 
Back
Top