Formatting separate lines of header

[Guest]

I have the following code to set the worksheet headers. All lines in the
header are printing in the set font size FS but I want to have a different
font size for each variable/line of the header. Any help would be much
appreciated. ts1

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
Dim CoName As String
Dim EffDate As String
Dim InsType As String

CoName = Range("Sheet1!b2").Value
EffDate = Range("Sheet1!b4").Value
InsType = "My Text String"
FS = 22

For Each wkSht In ThisWorkbook.Worksheets
wkSht.PageSetup.CenterHeader = "&""Arial,Bold""&" & FS & Chr(10) &
CoName & Chr(10) & InsType & Chr(10) & EffDate
Next wkSht
End Sub

 

[Tom Ogilvy]

Easiest and most specific solution is to
Turn on the macro recorder and set it up manually.
Then turn the macro recorder off and look at the code. It will show you
that you just embed more "Font" commands within the test string.

 

[Guest]

Tom,

Thanks for the direction. I did that whwien setting the header to simple
text strings but am still having problem with font commands preceding
variables which are being set in the code from values in one of the
worksheets.

ts1

 

[Tom Ogilvy]

You can test your results in the Immediate window in the VBE (under the View
menu if you don't have it visible)

? "abc """ & Range("B9").Value & """ efg" <cr>

for example.

Make work with your strings until you get the desired result. Obviously the
use of the & as a delimiter in the page formatting string makes it confusing
when you are concatenating.