Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Page Break Lines using VBA
I'd like to set page break lines and print area in a worksheet using VBA, but
have no idea how to do this... I have a worksheet with 11 pages (of the same size) when viewed in Page Break Preview: - Page 1 has a content table with 10 cells (B1:B10) where user can enter the report titles. - Page 2 shows contents under Title #1 (title name is entered in cell B1); Page 3 for Title #2; and so on. I'm hoping to only show and print Page 1, plus any other pages only if their titles have been entered in the content table. (e.g. if only 3 titles are entered, just show page 1 and the other 3 pages). Any help would be appreciated (preferably with example and explanation..because I'm just helpless with programming) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Page Break Lines using VBA
Sam,
I have written the following routine for you as per your specifications. You need to insert page breaks at the end of each of your ranges first. Let me know if you have any questions! Aran (Please rate this response) Private Sub CommandButton1_Click() var1 = Null var2 = Null var3 = Null var4 = Null var5 = Null var6 = Null var7 = Null var8 = Null var9 = Null var10 = Null If IsEmpty(Sheet1.Range("b1")) = True Then Else var1 = "$A$11:$f$20" & Chr(44) End If If IsEmpty(Sheet1.Range("b2")) = True Then Else var2 = "$A$21:$f$30" & Chr(44) End If If IsEmpty(Sheet1.Range("b3")) = True Then Else var3 = "$A$31:$f$40" & Chr(44) End If If IsEmpty(Sheet1.Range("b4")) = True Then Else var4 = "$A$41:$f$50" & Chr(44) End If If IsEmpty(Sheet1.Range("b5")) = True Then Else var5 = "$A$51:$f$60" & Chr(44) End If If IsEmpty(Sheet1.Range("b6")) = True Then Else var6 = "$A$61:$f$70" & Chr(44) End If If IsEmpty(Sheet1.Range("b7")) = True Then Else var7 = "$A$71:$f$80" & Chr(44) End If If IsEmpty(Sheet1.Range("b8")) = True Then Else var8 = "$A$81:$f$90" & Chr(44) End If If IsEmpty(Sheet1.Range("b9")) = True Then Else var9 = "$A$91:$f$100" & Chr(44) End If If IsEmpty(Sheet1.Range("b10")) = True Then Else var10 = "$A$101:$f$110" End If var11 = var1 & var2 & var3 & var4 & var5 & var6 & var7 & var8 & var9 & var10 ActiveSheet.PageSetup.PrintArea = var11 End Sub -- It wasnt Jesus it was just a fella! God Bless America! "Sam Kuo" wrote: I'd like to set page break lines and print area in a worksheet using VBA, but have no idea how to do this... I have a worksheet with 11 pages (of the same size) when viewed in Page Break Preview: - Page 1 has a content table with 10 cells (B1:B10) where user can enter the report titles. - Page 2 shows contents under Title #1 (title name is entered in cell B1); Page 3 for Title #2; and so on. I'm hoping to only show and print Page 1, plus any other pages only if their titles have been entered in the content table. (e.g. if only 3 titles are entered, just show page 1 and the other 3 pages). Any help would be appreciated (preferably with example and explanation..because I'm just helpless with programming) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Set Page Break Lines using VBA
Thank you Aran. Its great.
"Aran Black" wrote: Sam, I have written the following routine for you as per your specifications. You need to insert page breaks at the end of each of your ranges first. Let me know if you have any questions! Aran (Please rate this response) Private Sub CommandButton1_Click() var1 = Null var2 = Null var3 = Null var4 = Null var5 = Null var6 = Null var7 = Null var8 = Null var9 = Null var10 = Null If IsEmpty(Sheet1.Range("b1")) = True Then Else var1 = "$A$11:$f$20" & Chr(44) End If If IsEmpty(Sheet1.Range("b2")) = True Then Else var2 = "$A$21:$f$30" & Chr(44) End If If IsEmpty(Sheet1.Range("b3")) = True Then Else var3 = "$A$31:$f$40" & Chr(44) End If If IsEmpty(Sheet1.Range("b4")) = True Then Else var4 = "$A$41:$f$50" & Chr(44) End If If IsEmpty(Sheet1.Range("b5")) = True Then Else var5 = "$A$51:$f$60" & Chr(44) End If If IsEmpty(Sheet1.Range("b6")) = True Then Else var6 = "$A$61:$f$70" & Chr(44) End If If IsEmpty(Sheet1.Range("b7")) = True Then Else var7 = "$A$71:$f$80" & Chr(44) End If If IsEmpty(Sheet1.Range("b8")) = True Then Else var8 = "$A$81:$f$90" & Chr(44) End If If IsEmpty(Sheet1.Range("b9")) = True Then Else var9 = "$A$91:$f$100" & Chr(44) End If If IsEmpty(Sheet1.Range("b10")) = True Then Else var10 = "$A$101:$f$110" End If var11 = var1 & var2 & var3 & var4 & var5 & var6 & var7 & var8 & var9 & var10 ActiveSheet.PageSetup.PrintArea = var11 End Sub -- It wasnt Jesus it was just a fella! God Bless America! "Sam Kuo" wrote: I'd like to set page break lines and print area in a worksheet using VBA, but have no idea how to do this... I have a worksheet with 11 pages (of the same size) when viewed in Page Break Preview: - Page 1 has a content table with 10 cells (B1:B10) where user can enter the report titles. - Page 2 shows contents under Title #1 (title name is entered in cell B1); Page 3 for Title #2; and so on. I'm hoping to only show and print Page 1, plus any other pages only if their titles have been entered in the content table. (e.g. if only 3 titles are entered, just show page 1 and the other 3 pages). Any help would be appreciated (preferably with example and explanation..because I'm just helpless with programming) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page Break Lines in Excel 2007 | Excel Discussion (Misc queries) | |||
Make page break every several lines automatically by VBA | Excel Programming | |||
Can I set an automatic page breaks to break every 40 lines? | Excel Discussion (Misc queries) | |||
automatically put in page break after 14 lines. | Excel Programming | |||
Hatched page break lines | Excel Programming |