Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
Enter your name in cell A150 and then try your code.
Jim Cone San Francisco, USA http://www.realezsites.com/bus/primitivesoftware "JCIrish" wrote in message... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
JCIrish,
Not sure what you are trying to achieve with the code but you will only be to work with HPageBreaks(2), if it exists, hence the Subscript error. To work with all Hbreaks: Dim myHPageBreak As HPageBreak For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks With myHPageBreak Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual) Debug.Print "Location : " & .Location.Address(, , xlR1C1) End With Next End Sub "JCIrish" wrote in message ... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
Hi, NickHK
Thanks for the very useful response. I'm new at this. The last two lines of my code were put there simply to let me step through it several times, clearing the results and changing the HPageBreaks() subscript to observe the results. I just wanted to know where all the page breaks occur. I see the logic of what you've written and I will try that. I've not run into the code, "Debug.Print", so, not yet having tried it, I don't know what it does. Ditto for the line,"Next." Can you tell me something about these lines? Again, thanks a lot for giving a rookie a hand. JCIrish "NickHK" wrote: JCIrish, Not sure what you are trying to achieve with the code but you will only be to work with HPageBreaks(2), if it exists, hence the Subscript error. To work with all Hbreaks: Dim myHPageBreak As HPageBreak For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks With myHPageBreak Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual) Debug.Print "Location : " & .Location.Address(, , xlR1C1) End With Next End Sub "JCIrish" wrote in message ... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
JCIrish,
Debug (or Immediate) is one of the Windows you have available to see direct output from your app, or test results. If it is not visible, go to ViewImmediate Look there to see the result of any Debug.Print statements. As for "Next", it marks the end of the "For Each..." block. Check the help for loops. NickHK "JCIrish" wrote in message ... Hi, NickHK Thanks for the very useful response. I'm new at this. The last two lines of my code were put there simply to let me step through it several times, clearing the results and changing the HPageBreaks() subscript to observe the results. I just wanted to know where all the page breaks occur. I see the logic of what you've written and I will try that. I've not run into the code, "Debug.Print", so, not yet having tried it, I don't know what it does. Ditto for the line,"Next." Can you tell me something about these lines? Again, thanks a lot for giving a rookie a hand. JCIrish "NickHK" wrote: JCIrish, Not sure what you are trying to achieve with the code but you will only be to work with HPageBreaks(2), if it exists, hence the Subscript error. To work with all Hbreaks: Dim myHPageBreak As HPageBreak For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks With myHPageBreak Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual) Debug.Print "Location : " & .Location.Address(, , xlR1C1) End With Next End Sub "JCIrish" wrote in message ... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
Thanks again, NickHk. Since I recently started with VBA, winging my way
through it, I bought a book to help the process. I got hung up on this pageBreak problem, which is really tangential to my main concern, and your post was very helpful. I'm grateful. (I still can't, however, return page breaks that are automatic!) Thanks, JCIrish "NickHK" wrote: JCIrish, Debug (or Immediate) is one of the Windows you have available to see direct output from your app, or test results. If it is not visible, go to ViewImmediate Look there to see the result of any Debug.Print statements. As for "Next", it marks the end of the "For Each..." block. Check the help for loops. NickHK "JCIrish" wrote in message ... Hi, NickHK Thanks for the very useful response. I'm new at this. The last two lines of my code were put there simply to let me step through it several times, clearing the results and changing the HPageBreaks() subscript to observe the results. I just wanted to know where all the page breaks occur. I see the logic of what you've written and I will try that. I've not run into the code, "Debug.Print", so, not yet having tried it, I don't know what it does. Ditto for the line,"Next." Can you tell me something about these lines? Again, thanks a lot for giving a rookie a hand. JCIrish "NickHK" wrote: JCIrish, Not sure what you are trying to achieve with the code but you will only be to work with HPageBreaks(2), if it exists, hence the Subscript error. To work with all Hbreaks: Dim myHPageBreak As HPageBreak For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks With myHPageBreak Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual) Debug.Print "Location : " & .Location.Address(, , xlR1C1) End With Next End Sub "JCIrish" wrote in message ... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
JCIrish
Just a guess, but you do have more than 1 page to print, without any manual pagebreaks ? Otherwise, there will not be any automatic page breaks ? Also, how are you testing if they are automatic ? Something like my code below, but with xlPageBreakAutomatic ? NickHK "JCIrish" wrote in message ... Thanks again, NickHk. Since I recently started with VBA, winging my way through it, I bought a book to help the process. I got hung up on this pageBreak problem, which is really tangential to my main concern, and your post was very helpful. I'm grateful. (I still can't, however, return page breaks that are automatic!) Thanks, JCIrish "NickHK" wrote: JCIrish, Debug (or Immediate) is one of the Windows you have available to see direct output from your app, or test results. If it is not visible, go to ViewImmediate Look there to see the result of any Debug.Print statements. As for "Next", it marks the end of the "For Each..." block. Check the help for loops. NickHK "JCIrish" wrote in message ... Hi, NickHK Thanks for the very useful response. I'm new at this. The last two lines of my code were put there simply to let me step through it several times, clearing the results and changing the HPageBreaks() subscript to observe the results. I just wanted to know where all the page breaks occur. I see the logic of what you've written and I will try that. I've not run into the code, "Debug.Print", so, not yet having tried it, I don't know what it does. Ditto for the line,"Next." Can you tell me something about these lines? Again, thanks a lot for giving a rookie a hand. JCIrish "NickHK" wrote: JCIrish, Not sure what you are trying to achieve with the code but you will only be to work with HPageBreaks(2), if it exists, hence the Subscript error. To work with all Hbreaks: Dim myHPageBreak As HPageBreak For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks With myHPageBreak Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual) Debug.Print "Location : " & .Location.Address(, , xlR1C1) End With Next End Sub "JCIrish" wrote in message ... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
return pageBreaks
NickHK
Ah,yes.all that could be (especially multi pages to print without manual breaks). I'll try it out. Many Thanks JCIrish "NickHK" wrote: JCIrish Just a guess, but you do have more than 1 page to print, without any manual pagebreaks ? Otherwise, there will not be any automatic page breaks ? Also, how are you testing if they are automatic ? Something like my code below, but with xlPageBreakAutomatic ? NickHK "JCIrish" wrote in message ... Thanks again, NickHk. Since I recently started with VBA, winging my way through it, I bought a book to help the process. I got hung up on this pageBreak problem, which is really tangential to my main concern, and your post was very helpful. I'm grateful. (I still can't, however, return page breaks that are automatic!) Thanks, JCIrish "NickHK" wrote: JCIrish, Debug (or Immediate) is one of the Windows you have available to see direct output from your app, or test results. If it is not visible, go to ViewImmediate Look there to see the result of any Debug.Print statements. As for "Next", it marks the end of the "For Each..." block. Check the help for loops. NickHK "JCIrish" wrote in message ... Hi, NickHK Thanks for the very useful response. I'm new at this. The last two lines of my code were put there simply to let me step through it several times, clearing the results and changing the HPageBreaks() subscript to observe the results. I just wanted to know where all the page breaks occur. I see the logic of what you've written and I will try that. I've not run into the code, "Debug.Print", so, not yet having tried it, I don't know what it does. Ditto for the line,"Next." Can you tell me something about these lines? Again, thanks a lot for giving a rookie a hand. JCIrish "NickHK" wrote: JCIrish, Not sure what you are trying to achieve with the code but you will only be to work with HPageBreaks(2), if it exists, hence the Subscript error. To work with all Hbreaks: Dim myHPageBreak As HPageBreak For Each myHPageBreak In ActiveWorkbook.Worksheets(1).HPageBreaks With myHPageBreak Debug.Print "Manual ? : " & CBool(.Type = xlPageBreakManual) Debug.Print "Location : " & .Location.Address(, , xlR1C1) End With Next End Sub "JCIrish" wrote in message ... Here is a simple code I've written that returns page break locations, but only those page breaks that I've inserted manually. It won't return page breaks that Excel inserts automatically. If I try to get these page breaks I get the message, "subscript out of range. Can anybody tell me why? And how to use this type of simple code to get automatic page breaks? Sub pageBreakA1C1() 'will find only those page breaks that were manually inserted 'won't find those inserted automaticall by Excel Dim myPageBreakA1C1 myPageBreakA1C1 = ActiveWorkbook.Worksheets(1).HPageBreaks(2).Locati on.Address(, , xlR1C1) Range("A3").Select Selection = myPageBreakA1C1 Range("A4").Select Range("A3").ClearContents End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Deleting PageBreaks | Excel Programming | |||
Deleting PageBreaks | Excel Programming | |||
Pagebreaks are not made | Excel Programming | |||
pagebreaks | Excel Programming | |||
Excel pagebreaks | Excel Programming |