Named Range in Center Header

[Joel Mills]

I would like to be able to use a named range for the CenterHeader. When I
refer to one cell I can get it to work, but I'd like to refer to a named
range so that the users won't have to use "Alt+Enter" to force the text to
wrap. Instead I would like to have 4 cells referred to by name. Below is
the code that works. When I name a range "Center_Title" and select cells
C36 thru C39 and run the macro nothing appears in the center header. If I
can get this to work I want to do something similar for the Left, Center, &
Right Footer where some of the cells in the named range contain dates.

Sub PrintRange()

Dim PrintRange As Range
Set PrintRange = PrintArea("Curve")

With Worksheets("Curve").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange.Address(External:=True)
.Orientation = xlLandscape
.CenterHeader = "&""Arial,Regular""&22" &
Range("Instructions!C36").Value
PrintRange.BorderAround Weight:=xlThin
End With
End Sub

 

[Tom Ogilvy]

Dim sStr as String
sStr = ""
for each cell in Range("CenterHeader")
sStr = sStr & cell.Text & " "
Next
sStr = Trim(sStr)
With Worksheets("Curve").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange.Address(External:=True)
.Orientation = xlLandscape
.CenterHeader = "&""Arial,Regular""&22" & sStr
PrintRange.BorderAround Weight:=xlThin
End With
End Sub

 

[Joel Mills]

Tom thanks for the reply.....Wow! that was fast. This brings the named
range into the Center Header but doesn't wrap the text. I'm not sure if I
should name each cell as a separately or if there is a way to have the Title
wrap as it did when I force a return using "Atl+Enter".

 

[Tom Ogilvy]

Try Changing " " to vbNewline

That would be my best guess. If you mean the text in each of the cells is
multiline then I think that would involve a whole lot more complexity.


Dim sStr as String
sStr = ""
for each cell in Range("CenterHeader")
sStr = sStr & cell.Text & vbNewLine
Next
sStr = Trim(sStr)
With Worksheets("Curve").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange.Address(External:=True)
.Orientation = xlLandscape
.CenterHeader = "&""Arial,Regular""&22" & sStr
PrintRange.BorderAround Weight:=xlThin
End With
End Sub

 

[Joel Mills]

Tom this is very close to what I wanted. A blank line is added between each
line. I want it to wrap to the next line of text without a blank line.

 

[Tom Ogilvy]

Try Changing vbNewline to chr(10)
and add the line as shown below.

That would be my best guess. If you mean the text in each of the cells is
multiline then I think that would involve a whole lot more complexity.


Dim sStr as String
sStr = ""
for each cell in Range("CenterHeader")
sStr = sStr & cell.Text & chr(10)
Next
sStr = Left(sStr,len(sStr)-1)
sStr = Trim(sStr)
With Worksheets("Curve").PageSetup
.CenterHorizontally = True
.PrintArea = PrintRange.Address(External:=True)
.Orientation = xlLandscape
.CenterHeader = "&""Arial,Regular""&22" & sStr
PrintRange.BorderAround Weight:=xlThin
End With
End Sub

 

[Joel Mills]

It works perfectly. The text in each cell only contains one line. Thanks
for your help. I'm sure I should be able to adapt this to work for the
Footer as well.