Named Range in Center Header
 

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

Named Range in Center Header
 

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
--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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

Named Range in Center Header
 

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" wrote in message
...
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
--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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

Named Range in Center Header
 

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


--
Regards,
Tom Ogilvy

"Joel Mills" wrote in message
...
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" wrote in message
...
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
--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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

Named Range in Center Header
 

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" wrote in message
...
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


--
Regards,
Tom Ogilvy

"Joel Mills" wrote in message
...
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" wrote in message
...
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
--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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

Named Range in Center Header
 

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

--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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" wrote in message
...
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


--
Regards,
Tom Ogilvy

"Joel Mills" wrote in message
...
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" wrote in message
...
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
--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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

Named Range in Center Header
 

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.

"Tom Ogilvy" wrote in message
...
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

--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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" wrote in message
...
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


--
Regards,
Tom Ogilvy

"Joel Mills" wrote in message
...
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" wrote in message
...
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
--
Regards,
Tom Ogilvy


"Joel Mills" wrote in message
...
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