Using a cell value in the headers of a print out.

[My Name Is Tzu How Do You Do]

Hey guys. Can I use a cell value in my footer, just like worksheet names
and dates, etc. are incorporated? Thx.

 

[Dave Peterson]

You can either do it manually or use a macro to populate the header.

And you can even use an event macro that changes the header whenever a
particular cell changes.

The event would be different if the change is caused by the user typing
something or by a formula that recalculates.

If you want to try:

I'm gonna assume that the cell is changed by typing.

Rightclick on the worksheet tab that should have this behavior.
Select view code.
Paste this into the code window that just opened:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub 'single cell at a time
End If

If Intersect(Target, Me.Range("A1")) Is Nothing Then
Exit Sub 'check A1 (change the address to match the cell
End If

'this changes the left Footer
Me.PageSetup.LeftFooter = "Hi there: " & Target.Text

End Sub

======
You'll want to change that address (from A1) and the actual footer, too.

 

[My Name Is Tzu How Do You Do]

That is WAY freakin' overkill.

All I want is just like the DATE or the PAGE number can be put there, I
want to use a named range to name a cell, and place the value for it
there.

This should not require VB at all.

If it does, then MS needs to release a patch that has a few user
definable variables that are JUST LIKE the date and page variables.


It will be 100% pathetic if I have to add VB code to this worksheet,
just to get the revision number to pop in down at the header.

Just like &[Page] of &[Pages], it would be like
Prepared By &[AuthName], &[Date], &[DocName]

Where the user could define some print job variables for inclusion in
the document.


Thanks though. Shame I can't simply replace one of the existing
variables like the worksheet name variable data.

I can't believe this simple thing is not part of the baseline paradigm.
Hard to believe too that it likely will not get addressed either.

The "Print_Title" Named range is a perfect example of how it should be
offered. There should be a "Print_Sub" range name or such for the bottom
of the print job/sheet.

Someone should be able to inject a repeatable segment into each printed
page of a report/print job, just like the title headers repeat.

 

[My Name Is Tzu How Do You Do]

Doesn't this "code" force the workbook into being a macro enabled
workbook?

I wish to accomplish this by function, not VB.

Also, I want O2k3 compatibility.

 

[Gord Dibben]

You cannot get a cell value into a header/footer without using VBA.

Therefore............workbook must be macro-enabled.

Yes, the code is backward compatible.


Gord Dibben MS Excel MVP

 

[My Name Is Tzu How Do You Do]

It is pretty sad that MS cannot allow the headers and footers to contain
user configurable data without switching the workbook over to a macro
enabled workbook. Stupid, in fact.

VERY sad, in fact. It essentially means that the print engine facility
is an afterthought, or at least any refinement thereof is.

I should join the masses of folks that steal this barely useable
crapware, because despite getting billions of dollars a year, and having
crews of degreed assholes getting paid by it. It appears that none of
them can escape this "patch it up after the fact with VB" crap.

It essentially means that the real value of this software is not even
one tenth of what it sells for.

The assholes need to get off their asses and begin incorporating
features into the base package that does NOT cause a simple workbook to
be required to be macro enabled.

It points toward all those degrees being some lame "got my answers to
the final down at the pub" mentality.

Get off your asses and do something correctly for a change.

Printing facilities are separate from the cell array and data, and this
expensive package should have a better print facility. One which allows
the user to place graphics and text and CELL DATA in the print job that
are NOT defines "within the print area" of a cell array!