Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Range in Customer Header | Excel Discussion (Misc queries) | |||
How do I left justify center section header | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) | |||
Using a named range in a header with VBA? | Excel Discussion (Misc queries) | |||
align text in the center of a range of cells | Excel Programming |