| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Suppress First Page Header
I'm working in Excel 2002.
I would like to have a header appear on the second page and the pages that follow. The text that I want printed in the header beginning on page 2 is Unit Intake Report (continued). I have tried using the following coding with no success: Sub Test() Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With End Sub My document is protected. I've used the above coding sandwiched between: ActiveSheet.Unprotect ActiveSheet.Protect This still does not produce a header. Can I suppress the first page header within a protected document? Thanks -- Jamie |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jamie,
I just ran your exact code on a protected sheet with two pages. Ran and printed without a problem - with header printed on the second page only. (XL2002) Regards, Jim Cone San Francisco, USA "Jamie" wrote in message ... I'm working in Excel 2002. I would like to have a header appear on the second page and the pages that follow. The text that I want printed in the header beginning on page 2 is Unit Intake Report (continued). I have tried using the following coding with no success: Sub Test() Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With End Sub My document is protected. I've used the above coding sandwiched between: ActiveSheet.Unprotect ActiveSheet.Protect This still does not produce a header. Can I suppress the first page header within a protected document? Thanks Jamie |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Hi Jim to print the document I was using the print button. One of my
coworkers pointed out that I needed to print from within the macro itself clicking on the Run Sub/User Form. Doing that the document prints out great. Thanks for your help, I appreciate it. -- Jamie "Jim Cone" wrote: Jamie, I just ran your exact code on a protected sheet with two pages. Ran and printed without a problem - with header printed on the second page only. (XL2002) Regards, Jim Cone San Francisco, USA "Jamie" wrote in message ... I'm working in Excel 2002. I would like to have a header appear on the second page and the pages that follow. The text that I want printed in the header beginning on page 2 is Unit Intake Report (continued). I have tried using the following coding with no success: Sub Test() Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With End Sub My document is protected. I've used the above coding sandwiched between: ActiveSheet.Unprotect ActiveSheet.Protect This still does not produce a header. Can I suppress the first page header within a protected document? Thanks Jamie |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jamie,
You are welcome. A way to start the macro from the worksheet is to go to the menu bar... Tools | Macro | Macros... In the displayed list of macros, select your macro and click the "Run" button. Regards, Jim Cone San Francisco, USA "Jamie" wrote in message ... Hi Jim to print the document I was using the print button. One of my coworkers pointed out that I needed to print from within the macro itself clicking on the Run Sub/User Form. Doing that the document prints out great. Thanks for your help, I appreciate it. Jamie "Jim Cone" wrote: Jamie, I just ran your exact code on a protected sheet with two pages. Ran and printed without a problem - with header printed on the second page only. (XL2002) Regards, Jim Cone San Francisco, USA "Jamie" wrote in message ... I'm working in Excel 2002. I would like to have a header appear on the second page and the pages that follow. The text that I want printed in the header beginning on page 2 is Unit Intake Report (continued). I have tried using the following coding with no success: Sub Test() Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With End Sub My document is protected. I've used the above coding sandwiched between: ActiveSheet.Unprotect ActiveSheet.Protect This still does not produce a header. Can I suppress the first page header within a protected document? Thanks Jamie |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Hi Jim, thanks for your help. I have another question. Can I add coding
to the macro so the user can either use the print button or File, Print. I'd like to keep them out of Tools, Macro, Macros. Also I'd like to stay away from creating a print button on the worksheet. -- Jamie "Jim Cone" wrote: Jamie, You are welcome. A way to start the macro from the worksheet is to go to the menu bar... Tools | Macro | Macros... In the displayed list of macros, select your macro and click the "Run" button. Regards, Jim Cone San Francisco, USA "Jamie" wrote in message ... Hi Jim to print the document I was using the print button. One of my coworkers pointed out that I needed to print from within the macro itself clicking on the Run Sub/User Form. Doing that the document prints out great. Thanks for your help, I appreciate it. Jamie "Jim Cone" wrote: Jamie, I just ran your exact code on a protected sheet with two pages. Ran and printed without a problem - with header printed on the second page only. (XL2002) Regards, Jim Cone San Francisco, USA "Jamie" wrote in message ... I'm working in Excel 2002. I would like to have a header appear on the second page and the pages that follow. The text that I want printed in the header beginning on page 2 is Unit Intake Report (continued). I have tried using the following coding with no success: Sub Test() Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With End Sub My document is protected. I've used the above coding sandwiched between: ActiveSheet.Unprotect ActiveSheet.Protect This still does not produce a header. Can I suppress the first page header within a protected document? Thanks Jamie |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jamie,
You can use the "BeforePrint" event that Excel provides. If you do this every sheet in the workbook will use your code when it is printed... Jim Cone San Francisco, USA In the ThisWorkbook module add the following code: '------------------------------ Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Prevent normal printing. Cancel = True 'Call the macro with printing code. PrintTest End Sub '------------------------------ '------------------------------ In a standard module add the following code... (your code with some additional lines) '------------------------------------- Option Explicit Sub PrintTest() On Error GoTo PrintError ' New line Application.EnableEvents = False ' New line Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With PrintError: ' New line Application.EnableEvents = True ' New line End Sub '------------------------------------ '------------------------------------ "Jamie" wrote in message ... Hi Jim, thanks for your help. I have another question. Can I add coding to the macro so the user can either use the print button or File, Print. I'd like to keep them out of Tools, Macro, Macros. Also I'd like to stay away from creating a print button on the worksheet. Jamie "Jim Cone" wrote: Jamie, You are welcome. A way to start the macro from the worksheet is to go to the menu bar... Tools | Macro | Macros... In the displayed list of macros, select your macro and click the "Run" button. Regards, Jim Cone San Francisco, USA -snip- |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Hi Jim, me again. Thanks so much for your time I really appreciate it. I
inserted the coding you provided. I'm getting an error message in This Workbook. The error is: Compile error: Sub or Function not defined. The code that is highlighted is PrintTest. I've checked the coding and it matches yours. I wasn't sure if I was to include Option Explicit so I entered it, but with or without it I still receive the message. Do I need to include Option Explicit? Thanks so much! -- Jamie "Jim Cone" wrote: Jamie, You can use the "BeforePrint" event that Excel provides. If you do this every sheet in the workbook will use your code when it is printed... Jim Cone San Francisco, USA In the ThisWorkbook module add the following code: '------------------------------ Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Prevent normal printing. Cancel = True 'Call the macro with printing code. PrintTest End Sub '------------------------------ '------------------------------ In a standard module add the following code... (your code with some additional lines) '------------------------------------- Option Explicit Sub PrintTest() On Error GoTo PrintError ' New line Application.EnableEvents = False ' New line Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With PrintError: ' New line Application.EnableEvents = True ' New line End Sub '------------------------------------ '------------------------------------ "Jamie" wrote in message ... Hi Jim, thanks for your help. I have another question. Can I add coding to the macro so the user can either use the print button or File, Print. I'd like to keep them out of Tools, Macro, Macros. Also I'd like to stay away from creating a print button on the worksheet. Jamie "Jim Cone" wrote: Jamie, You are welcome. A way to start the macro from the worksheet is to go to the menu bar... Tools | Macro | Macros... In the displayed list of macros, select your macro and click the "Run" button. Regards, Jim Cone San Francisco, USA -snip- |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jamie,
The macro in the general module should be called "PrintTest", not the old name of "Test" ??? Jim Cone "Jamie" wrote in message ... Hi Jim, me again. Thanks so much for your time I really appreciate it. I inserted the coding you provided. I'm getting an error message in This Workbook. The error is: Compile error: Sub or Function not defined. The code that is highlighted is PrintTest. I've checked the coding and it matches yours. I wasn't sure if I was to include Option Explicit so I entered it, but with or without it I still receive the message. Do I need to include Option Explicit? Thanks so much! Jamie "Jim Cone" wrote: Jamie, You can use the "BeforePrint" event that Excel provides. If you do this every sheet in the workbook will use your code when it is printed... Jim Cone San Francisco, USA In the ThisWorkbook module add the following code: '------------------------------ Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Prevent normal printing. Cancel = True 'Call the macro with printing code. PrintTest End Sub '------------------------------ '------------------------------ In a standard module add the following code... (your code with some additional lines) '------------------------------------- Option Explicit Sub PrintTest() On Error GoTo PrintError ' New line Application.EnableEvents = False ' New line Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With PrintError: ' New line Application.EnableEvents = True ' New line End Sub '------------------------------------ '------------------------------------ |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jamie,
Further, The PrintTest code should "not" be in the sheet module. If it is in the sheet module you will get that error. In the VBE, you can go to the menu bar and click "Insert | Module" to get a standard module added to the project. And while Option Explicit is not required, it is good practice to include it. It will notify you of typos and force you to declare all variables..."Dim lngNumber as Long" etc. Regards, Jim Cone "Jim Cone" wrote in message ... Jamie, The macro in the general module should be called "PrintTest", not the old name of "Test" ??? Jim Cone "Jamie" wrote in message ... Hi Jim, me again. Thanks so much for your time I really appreciate it. I inserted the coding you provided. I'm getting an error message in This Workbook. The error is: Compile error: Sub or Function not defined. The code that is highlighted is PrintTest. I've checked the coding and it matches yours. I wasn't sure if I was to include Option Explicit so I entered it, but with or without it I still receive the message. Do I need to include Option Explicit? Thanks so much! Jamie "Jim Cone" wrote: Jamie, You can use the "BeforePrint" event that Excel provides. If you do this every sheet in the workbook will use your code when it is printed... Jim Cone San Francisco, USA In the ThisWorkbook module add the following code: '------------------------------ Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Prevent normal printing. Cancel = True 'Call the macro with printing code. PrintTest End Sub '------------------------------ '------------------------------ In a standard module add the following code... (your code with some additional lines) '------------------------------------- Option Explicit Sub PrintTest() On Error GoTo PrintError ' New line Application.EnableEvents = False ' New line Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With PrintError: ' New line Application.EnableEvents = True ' New line End Sub '------------------------------------ '------------------------------------ |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jim, thank you , thank you, thank you. It is working. I appreciate you
being so patient with me and continuing to answer my questions. Many thanks! -- Jamie "Jim Cone" wrote: Jamie, Further, The PrintTest code should "not" be in the sheet module. If it is in the sheet module you will get that error. In the VBE, you can go to the menu bar and click "Insert | Module" to get a standard module added to the project. And while Option Explicit is not required, it is good practice to include it. It will notify you of typos and force you to declare all variables..."Dim lngNumber as Long" etc. Regards, Jim Cone "Jim Cone" wrote in message ... Jamie, The macro in the general module should be called "PrintTest", not the old name of "Test" ??? Jim Cone "Jamie" wrote in message ... Hi Jim, me again. Thanks so much for your time I really appreciate it. I inserted the coding you provided. I'm getting an error message in This Workbook. The error is: Compile error: Sub or Function not defined. The code that is highlighted is PrintTest. I've checked the coding and it matches yours. I wasn't sure if I was to include Option Explicit so I entered it, but with or without it I still receive the message. Do I need to include Option Explicit? Thanks so much! Jamie "Jim Cone" wrote: Jamie, You can use the "BeforePrint" event that Excel provides. If you do this every sheet in the workbook will use your code when it is printed... Jim Cone San Francisco, USA In the ThisWorkbook module add the following code: '------------------------------ Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Prevent normal printing. Cancel = True 'Call the macro with printing code. PrintTest End Sub '------------------------------ '------------------------------ In a standard module add the following code... (your code with some additional lines) '------------------------------------- Option Explicit Sub PrintTest() On Error GoTo PrintError ' New line Application.EnableEvents = False ' New line Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With PrintError: ' New line Application.EnableEvents = True ' New line End Sub '------------------------------------ '------------------------------------ |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Oh Jim I spoke to soon. My workbook contains 12 sheets, one for each month.
For the January sheet I added info so I could get a second page to print, however, for February's sheet I left it as is ( 1 sheet long), but the coding forces a second page to print. The first page does not have a header, second page does. I'm thinking I need to have the coding check to see if there is a page break within the sheet, if so activate the LeftHeader coding, if not ignore the LeftHeader coding. Does that make sense? -- Jamie "Jim Cone" wrote: Jamie, Further, The PrintTest code should "not" be in the sheet module. If it is in the sheet module you will get that error. In the VBE, you can go to the menu bar and click "Insert | Module" to get a standard module added to the project. And while Option Explicit is not required, it is good practice to include it. It will notify you of typos and force you to declare all variables..."Dim lngNumber as Long" etc. Regards, Jim Cone "Jim Cone" wrote in message ... Jamie, The macro in the general module should be called "PrintTest", not the old name of "Test" ??? Jim Cone "Jamie" wrote in message ... Hi Jim, me again. Thanks so much for your time I really appreciate it. I inserted the coding you provided. I'm getting an error message in This Workbook. The error is: Compile error: Sub or Function not defined. The code that is highlighted is PrintTest. I've checked the coding and it matches yours. I wasn't sure if I was to include Option Explicit so I entered it, but with or without it I still receive the message. Do I need to include Option Explicit? Thanks so much! Jamie "Jim Cone" wrote: Jamie, You can use the "BeforePrint" event that Excel provides. If you do this every sheet in the workbook will use your code when it is printed... Jim Cone San Francisco, USA In the ThisWorkbook module add the following code: '------------------------------ Option Explicit Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Prevent normal printing. Cancel = True 'Call the macro with printing code. PrintTest End Sub '------------------------------ '------------------------------ In a standard module add the following code... (your code with some additional lines) '------------------------------------- Option Explicit Sub PrintTest() On Error GoTo PrintError ' New line Application.EnableEvents = False ' New line Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With PrintError: ' New line Application.EnableEvents = True ' New line End Sub '------------------------------------ '------------------------------------ |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Jamie,
Ok, some sheets have one page and others have 2 or more. This code mod always leaves page 1 with an empty header. If there is only one page to print, that is all you get. Also, check out the supplemental sub at the bottom. Regards, Jim Cone San Francisco, USA "----------------------------------- Sub PrintTest() On Error GoTo PrintError Application.EnableEvents = False Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") If TotPages 1 Then With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With Else ActiveSheet.PageSetup.LeftHeader = "" ActiveSheet.PrintOut End If PrintError: Application.EnableEvents = True End Sub '----------------------- 'If, for some reason, the code gets interrupted 'and doesn't run to the end, then run this... Sub ReturnToNormal() Application.EnableEvents = True End Sub "Jamie" wrote in message ... Oh Jim I spoke to soon. My workbook contains 12 sheets, one for each month. For the January sheet I added info so I could get a second page to print, however, for February's sheet I left it as is ( 1 sheet long), but the coding forces a second page to print. The first page does not have a header, second page does. I'm thinking I need to have the coding check to see if there is a page break within the sheet, if so activate the LeftHeader coding, if not ignore the LeftHeader coding. Does that make sense? -- Jamie |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Suppress First Page Header
Excellent Jim!! Thank you so much it is working beautifully.
-- Jamie "Jim Cone" wrote: Jamie, Ok, some sheets have one page and others have 2 or more. This code mod always leaves page 1 with an empty header. If there is only one page to print, that is all you get. Also, check out the supplemental sub at the bottom. Regards, Jim Cone San Francisco, USA "----------------------------------- Sub PrintTest() On Error GoTo PrintError Application.EnableEvents = False Dim TotPages As Long TotPages = Application.ExecuteExcel4Macro("GET.DOCUMENT(50)") If TotPages 1 Then With ActiveSheet.PageSetup .LeftHeader = "" ActiveSheet.PrintOut From:=1, To:=1 .LeftHeader = "Unit Intake Report (continued)" ActiveSheet.PrintOut From:=2, To:=TotPages End With Else ActiveSheet.PageSetup.LeftHeader = "" ActiveSheet.PrintOut End If PrintError: Application.EnableEvents = True End Sub '----------------------- 'If, for some reason, the code gets interrupted 'and doesn't run to the end, then run this... Sub ReturnToNormal() Application.EnableEvents = True End Sub "Jamie" wrote in message ... Oh Jim I spoke to soon. My workbook contains 12 sheets, one for each month. For the January sheet I added info so I could get a second page to print, however, for February's sheet I left it as is ( 1 sheet long), but the coding forces a second page to print. The first page does not have a header, second page does. I'm thinking I need to have the coding check to see if there is a page break within the sheet, if so activate the LeftHeader coding, if not ignore the LeftHeader coding. Does that make sense? -- Jamie |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Suppress First Page Numbering | Excel Worksheet Functions | |||
| How do I suppress the Header/Footer? | Excel Discussion (Misc queries) | |||
| How do you suppress the page number on page one? | Excel Discussion (Misc queries) | |||
| How do I suppress a header of footer on the first page of a docume | Setting up and Configuration of Excel | |||
| Suppress First Page Header | Excel Worksheet Functions | |||
Linear Mode
