page header multiple rows

[CG Rosén]

Good afternoon Group,

Trying to make something like the below to work. Not sure if it even is
possible.
Grateful for some hints.

Brgds

CG Rosén
----------------------------------------
From a column, text is loaded into str as below. The number of cells that
contains text
my vary from 1 to 10.

Dim str As Variant
str = Array(0, 1, 2, 3, 4, 5, 6, 7 ,8, 9)

For j = 1 To 10
str(j - 1) = Cells(j, 1)
Next j

if str() <> "" I would like to transfer the string to the
page header as lines with line feed. Cant figure out of to get the string
into the Setup.

With Sheets("Sheet1").PageSetup
.LeftHeader = "&8" & str(0) & Chr(10) & str(1) & Chr(10) &
str(2).......................................etc
End With

 

[JMB]

You could try:

Sub test()
Dim varTemp As Variant
Dim strTemp As String

varTemp = Array(0, 1, 2, 3, 4, 5, 6, 7, 8, 9)

For j = LBound(varTemp) To UBound(varTemp)
If Len(varTemp(j)) > 0 Then
strTemp = strTemp & varTemp(j) & Chr(10)
End If
Next j

If Len(strTemp) > 0 Then
strTemp = Left$(strTemp, Len(strTemp) - 1)
Sheets("Sheet1").PageSetup.LeftHeader = "&8 " & strTemp
End If
End Sub


Note that there is a space here "&8 ". This is to separate the font size
(8) from your header and to keep XL from thinking the font size s/b 80 (since
your header string begins with a 0). Alternatively, you could use a line
break

"&8" & Chr(10) & strTemp