Header formatting problem

[Guest]

I have 2 cells with values I want to print on a 2 line Center header with
different font sizes. The first value goes on line 1 of header with font size
22 and the second value on line 2 of header in font size 16. I can get two
literal text strings to format correctly but not the values in the following
cell references. I have also tried setting variables to the values and trying
to use them in the format statement. Can someone please provide the proper
syntax to get desired result. Thanks, Tom

Sheet1!b2
Sheet1!b4

I am having difficulty forming the proper syntax to the following statement:
--------------------------
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet

For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.CenterHeader = > ...

Next wkSht
End Sub
---------------------------

 

[Tom Ogilvy]

As I suggested, I recorded a two line center header set up as I wanted it.
I then modified the code to include values from Sheet1!B2 and Sheet1!B4
and this is what I modified it to:

Sub SetHeader(sh as Worksheet)
sStr = "&""Arial,Bold""&12Cell input is " & _
Range("Sheet1!B2").Text & _
" for line 1&""Arial,Regular""&10" & Chr(10) & _
"&""Arial,Italic""&8Cell input is" & _
Range("Sheet1!B4").Text & " for line 2"
With Sh.PageSetup
.CenterHeader = sStr
End With
End Sub

Put the above in a general module.
then you can set up your code as

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet

For Each wkSht In ThisWorkbook.Worksheets

SetHeader wkSht

Next wkSht
End Sub

 

[retseort]

Tom,

I used your script you supplied with success. Although I moved it t
the Left Header. Now I need to do the same and populate the righ
header as well. Can you show me how you would extend the script yo
wrote to perform this in the left headr and then continue placing inf
in the right header? Center Header would be blank in this case.

When done I should have the left header showing the Document Title an
Revison date stacked upon one another and on the right I should hav
the author and approval date.

Thanks
Da