Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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


  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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




  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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






  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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








  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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












  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 27,285
Default 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












  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 79
Default 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














Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Named Range in Customer Header Rebecca_SUNY Excel Discussion (Misc queries) 1 April 13th 09 10:37 PM
How do I left justify center section header nick Excel Discussion (Misc queries) 1 September 6th 06 09:30 PM
inserting a named range into new cells based on a named cell Peter S. Excel Discussion (Misc queries) 1 June 4th 06 03:53 AM
Using a named range in a header with VBA? [email protected] Excel Discussion (Misc queries) 5 October 27th 05 10:52 AM
align text in the center of a range of cells nikolaosk[_9_] Excel Programming 4 October 19th 03 09:44 AM


All times are GMT +1. The time now is 01:24 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"