Named Range in Center Header

  • Thread starter Thread starter Joel Mills
  • Start date Start date
J

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
 
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
 
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".
 
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
 
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.
 
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
 
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.
 
Back
Top