Sub SetHeader(sh As Worksheet)
Dim sStr As String
Dim sStr1 As String
sStr = "&""Arial,Bold""&12Title: " & _
Range("Sheet1!B2").Text & _
Chr(10) & _
"&""Arial,Italic""&8Revision Date: " & _
Range("Sheet1!B4").Text
sStr1 = "&""Arial,Bold""&12Author: " & _
Range("Sheet1!B6").Text & _
Chr(10) & _
"&""Arial,Italic""&8Approval Date: " & _
Range("Sheet1!B8").Text
With sh.PageSetup
.LeftHeader = sStr
.CenterHeader = ""
.RightHeader = sStr1
End With
End Sub
worked for me.
--
Regards,
Tom Ogilvy
"retseort" wrote in
message ...
Tom,
I used your script you supplied with success. Although I moved it to
the Left Header. Now I need to do the same and populate the right
header as well. Can you show me how you would extend the script you
wrote to perform this in the left headr and then continue placing info
in the right header? Center Header would be blank in this case.
When done I should have the left header showing the Document Title and
Revison date stacked upon one another and on the right I should have
the author and approval date.
Thanks
Dan
Original message
Header formatting problem
--------------------------------------------------------------------------
------
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
--
Regards,
Tom Ogilvy
--
retseort
------------------------------------------------------------------------
retseort's Profile:
http://www.excelforum.com/member.php...o&userid=24690
View this thread: http://www.excelforum.com/showthread...hreadid=480587