| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel
I am trying to program a macro in Excel that when you run it, it puts the
date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
This is Excel 2003 running on Windows Server 2003. Thanks!
"KnightRiderAW" wrote: I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Is this what you mean?
ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Unfortunately, this doesn't work. I also tried to do variations on what you
had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
It does exactly what I thought you were trying to do, so I guess I don't
understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Bob,
What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Aaron,
Not for me it doesn't. Are you wanting it to be automatic, at print time, or will you run that macro for each worksheet? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Bob,
Our accounting staff are required to place these items on all of there Excel and Word Printouts for tracking purposes. I am creating a toolbar button that they can push that will automate this step for them. When they click on Print Preview (or even print it), we don't want a default name to show up, but if the name is not the original default name, we do want it to appear. I hope this helps. I'll try again to make sure I have my coding correct and that I haven't done anything wrong. Thanks again for the help! Aaron "Bob Phillips" wrote: Aaron, Not for me it doesn't. Are you wanting it to be automatic, at print time, or will you run that macro for each worksheet? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Aaron,
You can use this code which will do it automatically, no need to have a toolbar button, or get them to push it. Put this code in everyone's personal.xls, or create an add-in Option Explicit Private WithEvents app As Application Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) With Wb.ActiveSheet If .Name = .CodeName Then .PageSetup.CenterHeader = "" Else .PageSetup.CenterHeader = "&A" End If With .PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" .RightFooter = "&Z&F" End With End With End Sub Private Sub Workbook_Open() Set app = Application End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, Our accounting staff are required to place these items on all of there Excel and Word Printouts for tracking purposes. I am creating a toolbar button that they can push that will automate this step for them. When they click on Print Preview (or even print it), we don't want a default name to show up, but if the name is not the original default name, we do want it to appear. I hope this helps. I'll try again to make sure I have my coding correct and that I haven't done anything wrong. Thanks again for the help! Aaron "Bob Phillips" wrote: Aaron, Not for me it doesn't. Are you wanting it to be automatic, at print time, or will you run that macro for each worksheet? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Bob,
Thanks for the coding, but I still want it to be a button that they click. I already have the toolbar button set up without any problems. I erased all of my coding and placed your original coding into the macro and it did exactly the same thing. It still brings up Sheet1 at the top of the page on a new workbook. Any other thoughts? "Bob Phillips" wrote: Aaron, You can use this code which will do it automatically, no need to have a toolbar button, or get them to push it. Put this code in everyone's personal.xls, or create an add-in Option Explicit Private WithEvents app As Application Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) With Wb.ActiveSheet If .Name = .CodeName Then .PageSetup.CenterHeader = "" Else .PageSetup.CenterHeader = "&A" End If With .PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" .RightFooter = "&Z&F" End With End With End Sub Private Sub Workbook_Open() Set app = Application End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, Our accounting staff are required to place these items on all of there Excel and Word Printouts for tracking purposes. I am creating a toolbar button that they can push that will automate this step for them. When they click on Print Preview (or even print it), we don't want a default name to show up, but if the name is not the original default name, we do want it to appear. I hope this helps. I'll try again to make sure I have my coding correct and that I haven't done anything wrong. Thanks again for the help! Aaron "Bob Phillips" wrote: Aaron, Not for me it doesn't. Are you wanting it to be automatic, at print time, or will you run that macro for each worksheet? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Excel
Why would you want a button if it were automatic? Doesn't make sense to me.
Have you changed the codename of the sheets? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, Thanks for the coding, but I still want it to be a button that they click. I already have the toolbar button set up without any problems. I erased all of my coding and placed your original coding into the macro and it did exactly the same thing. It still brings up Sheet1 at the top of the page on a new workbook. Any other thoughts? "Bob Phillips" wrote: Aaron, You can use this code which will do it automatically, no need to have a toolbar button, or get them to push it. Put this code in everyone's personal.xls, or create an add-in Option Explicit Private WithEvents app As Application Private Sub app_WorkbookBeforePrint(ByVal Wb As Workbook, Cancel As Boolean) With Wb.ActiveSheet If .Name = .CodeName Then .PageSetup.CenterHeader = "" Else .PageSetup.CenterHeader = "&A" End If With .PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" .RightFooter = "&Z&F" End With End With End Sub Private Sub Workbook_Open() Set app = Application End Sub 'This is workbook event code. 'To input this code, right click on the Excel icon on the worksheet '(or next to the File menu if you maximise your workbooks), 'select View Code from the menu, and paste the code -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, Our accounting staff are required to place these items on all of there Excel and Word Printouts for tracking purposes. I am creating a toolbar button that they can push that will automate this step for them. When they click on Print Preview (or even print it), we don't want a default name to show up, but if the name is not the original default name, we do want it to appear. I hope this helps. I'll try again to make sure I have my coding correct and that I haven't done anything wrong. Thanks again for the help! Aaron "Bob Phillips" wrote: Aaron, Not for me it doesn't. Are you wanting it to be automatic, at print time, or will you run that macro for each worksheet? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Bob, What I am trying to do is make so that the Excel Tab name will appear at the top center of the page when you print. However, if it has the default name (Sheet1, Sheet2, etc.), I don't want to appear at all. When I put your coding suggestion into the macro, it still shows Sheet1 at the top of the page. I am trying to get rid of that only if it is the default. Thanks! Aaron "Bob Phillips" wrote: It does exactly what I thought you were trying to do, so I guess I don't understand what it is you want. What are you trying to do exactly? -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... Unfortunately, this doesn't work. I also tried to do variations on what you had suggested by removing the CenterHeader Command prior to the IF THEN statement and also adding a period in front of the first CenterHeader Command in the IF THEN statement. None of the three options worked. They still list the Sheet1 at the top of the page. Any other suggestions? Thanks! Aaron "Bob Phillips" wrote: Is this what you mean? ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If ActiveSheet.Name = ActiveSheet.CodeName Then CenterHeader = "" Else .CenterHeader = "&A" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With -- HTH RP (remove nothere from the email address if mailing direct) "KnightRiderAW" wrote in message ... I am trying to program a macro in Excel that when you run it, it puts the date and time in the top-left header and the pathname in the bottom right footer. I also want the worksheet in the center header. I have it where all of this takes place. However, I want it to not label the center header if it is the default name (Sheet1, Sheet2, Sheet3, etc.). Below is the programming I have done, but I can't seem to get the Default name not to show. Can anyone give me some help or advice. Thanks! ActiveSheet.PageSetup.PrintArea = "" With ActiveSheet.PageSetup .LeftHeader = "&D" & Chr(10) & "&T" .CenterHeader = "&A" If CenterHeader = "Sheet1" Then CenterHeader = "" End If If CenterHeader = "Sheet2" Then CenterHeader = "" End If If CenterHeader = "Sheet3" Then CenterHeader = "" End If .RightHeader = "" .LeftFooter = "" .CenterFooter = "" .RightFooter = "&Z&F" .LeftMargin = Application.InchesToPoints(0.75) .RightMargin = Application.InchesToPoints(0.75) .TopMargin = Application.InchesToPoints(1) .BottomMargin = Application.InchesToPoints(1) .HeaderMargin = Application.InchesToPoints(0.5) .FooterMargin = Application.InchesToPoints(0.5) .PrintHeadings = False .PrintGridlines = False .PrintComments = xlPrintNoComments .CenterHorizontally = False .CenterVertically = False .Orientation = xlPortrait .Draft = False .PaperSize = xlPaperLetter .FirstPageNumber = xlAutomatic .Order = xlDownThenOver .BlackAndWhite = False .Zoom = 100 .PrintErrors = xlPrintErrorsDisplayed End With End Sub Aaron |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Linear Mode
