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

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" <[email protected]> wrote in message
news:%[email protected]...
> 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 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:%[email protected]...
> > 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
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:%[email protected]...
> > > 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 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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:%[email protected]...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:%[email protected]...
> > > > > 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
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>

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" <[email protected]> wrote in message
news:%[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
> > 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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" <[email protected]> wrote in message
> > > news:[email protected]...
> > > > 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" <[email protected]> wrote in message
> > > > news:[email protected]...
> > > > > 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" <[email protected]> wrote in message
> > > > > news:%[email protected]...
> > > > > > 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
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > >
> > > >
> > >
> > >
> >
> >
>
>