Use a cell's content as part of Header
 

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=512417

Use a cell's content as part of Header
 

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

swatsp0p wrote:

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

--
swatsp0p

------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=512417

--

Dave Peterson

Use a cell's content as part of Header
 

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" wrote in
message ...

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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile:
http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=512417

Use a cell's content as part of Header
 

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 tabView 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


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=512417

Use a cell's content as part of Header
 

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

swatsp0p wrote:

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 tabView 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

--
swatsp0p

------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=512417

--

Dave Peterson

Use a cell's content as part of Header
 

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

Works as desired, now.

Cheers.

Bruce


--
swatsp0p


------------------------------------------------------------------------
swatsp0p's Profile: http://www.excelforum.com/member.php...o&userid=15101
View this thread: http://www.excelforum.com/showthread...hreadid=512417