Use a cell's content as part of Header

[swatsp0p]

OK, gang, help me out here, please. I have researched several similar
threads that don't completely address the question. I have seen
multiple responses that show how to make a cell's contents the Header,
e.g.:

Code:
--------------------

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.CenterHeader = ActiveSheet.Range("I3").Value
End With
End Sub

--------------------


However, I have not found one that has addressed the situation where a
cell reference is just a -part- of the Header. e.g. I would like a
header that combines some text and pulls a date from a cell. I have a
formula that creates the desired output in a cell:
="TIME SHEET DATA, "&TEXT(DATE(YEAR(I3),MONTH(I3),DAY(I3)),"MMMM")&"
"&YEAR(I3)
and I can use it as "Print Titles...Rows to Repeat at Top", but this is
not my first choice.

Is there a way to incorporate all of this in the header via code (and
make it dynamic)?

Thanks in advance for your insight and guidance.

Bruce

 

[Dave Peterson]

I think I'd be specific about which sheet, too:

="TIME SHEET DATA, "&TEXT(DATE(YEAR(I3),MONTH(I3),DAY(I3)),"MMMM")&"
"&YEAR(I3)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
dim myStr as string
With me.worksheets("sheet1")
myStr = "Time Sheet Data, " & format(.range("i3").value, "mmmm yyyy")
.PageSetup.CenterHeader = mystr
End With
End Sub

 

[Bob Phillips]

Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveSheet.PageSetup
.CenterHeader = Format(ActiveSheet.Range("I3"), """TIME SHEET
DATA,""MMMMYYYY")
End With
End Sub



--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[swatsp0p]

Dave and Bob: Thank you both for your replies. I have tried both
examples, and still no header appears (either in print preview or on
the actual printout).

I pasted your codes (adjusting sheet reference as needed to "sheet3")
into the sheet3 General window (right click on sheet tab>View Code and
paste there). Placing this code in "this workbook" doesn't work either
(of course).

What am I still missing? (I'm sure I'll slap my forehead when I see how
easy this should have been for me).

Again, thank you for your time.

Bruce

 

[Dave Peterson]

Actually, since this is workbook_beforeprint, the code goes into the
ThisWorkbook module.

 

[swatsp0p]

Thanks, Dave. Not sure why it didn't work for me yesterday. Maybe the
stars were not aligned?

Works as desired, now.

Cheers.

Bruce