Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Header formatting problem
I have 2 cells with values I want to print on a 2 line Center header with
different font sizes. The first value goes on line 1 of header with font size 22 and the second value on line 2 of header in font size 16. I can get two literal text strings to format correctly but not the values in the following cell references. I have also tried setting variables to the values and trying to use them in the format statement. Can someone please provide the proper syntax to get desired result. Thanks, Tom Sheet1!b2 Sheet1!b4 I am having difficulty forming the proper syntax to the following statement: -------------------------- Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ThisWorkbook.Worksheets wkSht.PageSetup.CenterHeader = ... Next wkSht End Sub --------------------------- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Header formatting problem
As I suggested, I recorded a two line center header set up as I wanted it.
I then modified the code to include values from Sheet1!B2 and Sheet1!B4 and this is what I modified it to: Sub SetHeader(sh as Worksheet) sStr = "&""Arial,Bold""&12Cell input is " & _ Range("Sheet1!B2").Text & _ " for line 1&""Arial,Regular""&10" & Chr(10) & _ "&""Arial,Italic""&8Cell input is" & _ Range("Sheet1!B4").Text & " for line 2" With Sh.PageSetup .CenterHeader = sStr End With End Sub Put the above in a general module. then you can set up your code as Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ThisWorkbook.Worksheets SetHeader wkSht Next wkSht End Sub -- Regards, Tom Ogilvy "ts1" wrote in message ... I have 2 cells with values I want to print on a 2 line Center header with different font sizes. The first value goes on line 1 of header with font size 22 and the second value on line 2 of header in font size 16. I can get two literal text strings to format correctly but not the values in the following cell references. I have also tried setting variables to the values and trying to use them in the format statement. Can someone please provide the proper syntax to get desired result. Thanks, Tom Sheet1!b2 Sheet1!b4 I am having difficulty forming the proper syntax to the following statement: -------------------------- Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim wkSht As Worksheet For Each wkSht In ThisWorkbook.Worksheets wkSht.PageSetup.CenterHeader = ... Next wkSht End Sub --------------------------- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Header formatting problem
Tom, I used your script you supplied with success. Although I moved it t the Left Header. Now I need to do the same and populate the righ header as well. Can you show me how you would extend the script yo wrote to perform this in the left headr and then continue placing inf in the right header? Center Header would be blank in this case. When done I should have the left header showing the Document Title an Revison date stacked upon one another and on the right I should hav the author and approval date. Thanks Da -- retseor ----------------------------------------------------------------------- retseort's Profile: http://www.excelforum.com/member.php...fo&userid=2469 View this thread: http://www.excelforum.com/showthread.php?threadid=47918 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problem W Header | Excel Discussion (Misc queries) | |||
Header Problem | Excel Discussion (Misc queries) | |||
Formatting Codes for header and footer | Excel Programming | |||
Formatting a header with VBA | Excel Programming | |||
Header problem! | Excel Programming |