| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
BeforePrint help
Private Sub Workbook_BeforePrint(Cancel As Boolean)
With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
BeforePrint help
Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
BeforePrint help
Sorry did not change your code to set the header on the specific
sheet........ Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&Pof " & N Else wks.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next -- Cheers Nigel "Nigel" wrote in message ... Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
BeforePrint help
Thanks Nigel
I think this is beginning to make sense. Lou "Nigel" wrote: Sorry did not change your code to set the header on the specific sheet........ Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&Pof " & N Else wks.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next -- Cheers Nigel "Nigel" wrote in message ... Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
BeforePrint help
Final:
Private Sub Workbook_BeforePrint(Cancel As Boolean) 'Add Header Macro N = WorkSheets("Cover").Range("MyPages").Value Dim wks As Worksheet For Each wks In ActiveWorkbook.WorkSheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular"_ "&8Page &P of " & N Else wks.PageSetup.RightHeader = "" End If Next End Sub It works PERFECTLY ... WOOHOO!!! Thanks again to both you and Tom Health and Happiness Lou "Rookie 1st class" wrote: Thanks Nigel I think this is beginning to make sense. Lou "Nigel" wrote: Sorry did not change your code to set the header on the specific sheet........ Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Range("A1") = 1 Then wks.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&Pof " & N Else wks.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next -- Cheers Nigel "Nigel" wrote in message ... Put a loop around the header set up as follows.......... Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page&P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If Next --- Cheers Nigel "Rookie 1st class" <Rookie1stClass@SpamThis wrote in message ... Private Sub Workbook_BeforePrint(Cancel As Boolean) With ActiveWorkbook.Sheets N = WorkSheets("Cover").Range("MyPages").Value If Range("A1") = 1 Then ActiveSheet.PageSetup.RightHeader = "&""Arial Narrow,Regular""&8Page &P of " & N Else ActiveSheet.PageSetup.RightHeader = "" '< Runs out back door hugs a few trees End If End With End Sub Works but only changes heading on active sheet. I want to change the header on every sheet (5 or 9 depending). I don't want to name the workbook as it may change. Path may change as well. Do I need loop statement? Quite happy I got this far. The P is actually [P] - [PAGES], is there a way to easily display these hidden characters? I found "VB help, with, control flow keyword summary", seems to help. Any other suggested sources? Thanks Lou |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| BeforePrint Event | Excel Programming | |||
| ThisWorkbook BeforePrint | Excel Programming | |||
| BeforePrint Add In | Excel Programming | |||
| ADO 2.7 & ADO 2.8 beforeprint | Excel Programming | |||
| Help with BeforePrint | Excel Programming | |||
Hybrid Mode
