|
Print selection using check boxes
The code shown below works "correctly" in printing the selected sheets.
However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub |
Print selection using check boxes
Also, it is printing the sheet with all of the buttons and check boxes.
Is there a way to prevent it from printing that sheet? "Aaron" wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub |
Print selection using check boxes
Do a search on .PrintOut and see if there's something there you can use.
-- HTH, Barb Reinhardt "Aaron" wrote: Also, it is printing the sheet with all of the buttons and check boxes. Is there a way to prevent it from printing that sheet? "Aaron" wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub |
Print selection using check boxes
Thank you Barb.
There is a post titled "CheckBox to select pages to print?" from 9/1/2007 It seems similar. Do you think that is the direction I should head? "Barb Reinhardt" wrote: Do a search on .PrintOut and see if there's something there you can use. -- HTH, Barb Reinhardt "Aaron" wrote: Also, it is printing the sheet with all of the buttons and check boxes. Is there a way to prevent it from printing that sheet? "Aaron" wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub |
Print selection using check boxes
If you use this one line:
Sheets(array("ClientData","W1","Fee")).PrintOut Do you get one file? And what happens if checkbox28 and checkbox31 are each checked? Your existing code would print the same sheet multiple times. Aaron wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub -- Dave Peterson |
Print selection using check boxes
ps.
If you only get one file, you can use this kind of code to build an array of names that should be printed: Option Explicit Private Sub PrintSelected_Click() Dim sCtr As Long Dim SheetNames() As String Dim wks As Worksheet Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Clientdata" End If If CheckBox2 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "w1" End If If CheckBox3 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "fee" End If 'I got bored here! If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If End Sub Dave Peterson wrote: If you use this one line: Sheets(array("ClientData","W1","Fee")).PrintOut Do you get one file? And what happens if checkbox28 and checkbox31 are each checked? Your existing code would print the same sheet multiple times. Aaron wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
Thanks! I will work on it. I understand the bordom I used Excel to write
the code the first time. "Dave Peterson" wrote: ps. If you only get one file, you can use this kind of code to build an array of names that should be printed: Option Explicit Private Sub PrintSelected_Click() Dim sCtr As Long Dim SheetNames() As String Dim wks As Worksheet Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Clientdata" End If If CheckBox2 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "w1" End If If CheckBox3 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "fee" End If 'I got bored here! If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If End Sub Dave Peterson wrote: If you use this one line: Sheets(array("ClientData","W1","Fee")).PrintOut Do you get one file? And what happens if checkbox28 and checkbox31 are each checked? Your existing code would print the same sheet multiple times. Aaron wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
So I think I have it for the most part.
Most of the code is for printing the report or the proposal for some financial work I do. There are 2 sections in the original code I posted where it is selecting sheets to be printed for technical review based on the content of the sheets. These are to be printed in addition to default sheets to be printed. How do I revise this to your suggested code to get the correct effect? If CheckBox31 = True Then Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut End If "Dave Peterson" wrote: ps. If you only get one file, you can use this kind of code to build an array of names that should be printed: Option Explicit Private Sub PrintSelected_Click() Dim sCtr As Long Dim SheetNames() As String Dim wks As Worksheet Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Clientdata" End If If CheckBox2 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "w1" End If If CheckBox3 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "fee" End If 'I got bored here! If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If End Sub Dave Peterson wrote: If you use this one line: Sheets(array("ClientData","W1","Fee")).PrintOut Do you get one file? And what happens if checkbox28 and checkbox31 are each checked? Your existing code would print the same sheet multiple times. Aaron wrote: The code shown below works "correctly" in printing the selected sheets. However, it is printing each sheet separately. i.e. when I print to PDF it produces multiple files instead of one file. Any suggestions for putting the output into one file? Your help is greatly appreciated! Private Sub PrintSelected_Click() Application.Dialogs(xlDialogPrint).Show If CheckBox1 = True Then Sheets("ClientData").PrintOut End If If CheckBox2 = True Then Sheets("W1").PrintOut End If If CheckBox3 = True Then Sheets("Fee").PrintOut End If If CheckBox4 = True Then Sheets("Data").PrintOut End If If CheckBox5 = True Then Sheets("B1").PrintOut End If If CheckBox6 = True Then Sheets("B2").PrintOut End If If CheckBox7 = True Then Sheets("B3").PrintOut End If If CheckBox8 = True Then Sheets("B4").PrintOut End If If CheckBox9 = True Then Sheets("B5").PrintOut End If If CheckBox10 = True Then Sheets("B6").PrintOut End If If CheckBox11 = True Then Sheets("B7").PrintOut End If If CheckBox12 = True Then Sheets("B8").PrintOut End If If CheckBox13 = True Then Sheets("B9").PrintOut End If If CheckBox14 = True Then Sheets("B10").PrintOut End If If CheckBox15 = True Then Sheets("B11").PrintOut End If If CheckBox16 = True Then Sheets("B12").PrintOut End If If CheckBox17 = True Then Sheets("B13").PrintOut End If If CheckBox18 = True Then Sheets("B14").PrintOut End If If CheckBox19 = True Then Sheets("B15").PrintOut End If If CheckBox20 = True Then Sheets("Combined").PrintOut End If If CheckBox21 = True Then Sheets("Summary").PrintOut End If If CheckBox22 = True Then Sheets("Results").PrintOut End If If CheckBox23 = True Then Sheets("Bar").PrintOut End If If CheckBox24 = True Then Sheets("Pie").PrintOut End If If CheckBox25 = True Then Sheets("Cash").PrintOut End If If CheckBox26 = True Then Sheets("NPV").PrintOut End If If CheckBox27 = True Then Sheets("EX I").PrintOut End If If CheckBox30 = True Then Sheets("Summary").PrintOut End If If CheckBox28 = True Then Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If If CheckBox29 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim sh As Worksheet For Each sh In ThisWorkbook.Worksheets If sh.Visible = -1 Then If sh.Range("B71").Value < "" Then sh.PrintOut End If End If Next sh End If If CheckBox31 = True Then Sheets("Summary").PrintOut Sheets("ClientData").PrintOut Sheets("W1").PrintOut Sheets("Fee").PrintOut Sheets("Data").PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets("Results").PrintOut Sheets("bar").PrintOut Sheets("pie").PrintOut Sheets("Cash").PrintOut Sheets("NPV").PrintOut Sheets("Ex I").PrintOut End If End Sub -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
Did you test that .printout line to see if you really got a single file. If you
got multiple files, then you're wasting your time with this approach! Since you're building an array of sheet names, you could check to see if the worksheet name is already in the array. Dim Res as variant 'you only need to declare wks once and you can reuse it in both loops 'you don't need sh or she dim wks as worksheet .....lots of code For Each wks In ThisWorkbook.Worksheets If wks.Visible = -1 Then If wks.Range("B71").Value < "" Then if sctr = 0 then 'no names in array yet, so just add it sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name else 'there are names in the array, so check to see if that one is 'is in the array res = application.match(wks.name, sheetnames,0) if isnumeric(res) then 'already there, so don't add it again else 'not there, so add it. sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name end if end if end if end if next wks and you'd still print with something like: If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If Aaron wrote: So I think I have it for the most part. Most of the code is for printing the report or the proposal for some financial work I do. There are 2 sections in the original code I posted where it is selecting sheets to be printed for technical review based on the content of the sheets. These are to be printed in addition to default sheets to be printed. How do I revise this to your suggested code to get the correct effect? If CheckBox31 = True Then Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut End If <<snipped |
Print selection using check boxes
That code is great and it works but your code and your comments have made me
realize I need to change my approach. I need to change the code checking cell B71 to only check sheets "B1" through "B15". Also in the section of code you wrote on this how can I include some sheets that need printed by default. There are default sheets that should be printed in order both before and after the sheets being evaluated in for content in cell B71. Also, do you have any ideas on how to keep the sheet with all the check boxes from being printed. Thank you, Aaron "Dave Peterson" wrote: Did you test that .printout line to see if you really got a single file. If you got multiple files, then you're wasting your time with this approach! Since you're building an array of sheet names, you could check to see if the worksheet name is already in the array. Dim Res as variant 'you only need to declare wks once and you can reuse it in both loops 'you don't need sh or she dim wks as worksheet .....lots of code For Each wks In ThisWorkbook.Worksheets If wks.Visible = -1 Then If wks.Range("B71").Value < "" Then if sctr = 0 then 'no names in array yet, so just add it sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name else 'there are names in the array, so check to see if that one is 'is in the array res = application.match(wks.name, sheetnames,0) if isnumeric(res) then 'already there, so don't add it again else 'not there, so add it. sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name end if end if end if end if next wks and you'd still print with something like: If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If Aaron wrote: So I think I have it for the most part. Most of the code is for printing the report or the proposal for some financial work I do. There are 2 sections in the original code I posted where it is selecting sheets to be printed for technical review based on the content of the sheets. These are to be printed in addition to default sheets to be printed. How do I revise this to your suggested code to get the correct effect? If CheckBox31 = True Then Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut End If <<snipped |
Print selection using check boxes
The simple answer first (I think!).
If the way the worksheet with the checkbox gets included is through one of those loops, you can avoid that worksheet by looking at the name. For Each wks In ThisWorkbook.Worksheets if wks.name = me.name then 'do nothing else If wks.Range("B71").Value < "" Then .... ========== If you always have worksheets named B1 to B17, you could use: dim wCtr as long 'near the top with the other Dim Statements ..... for wctr = 1 to 17 set wks = worksheets("B" & wctr) if wks.name = me.name then 'do nothing else if wks.range("b17").value < "" then ... end if next wctr You won't loop through all the worksheets--you'll just look at B1 to B17. I'm not sure what you mean by the worksheets that print by default. If you mean that they always print, you could use: 'say you have 3 sheets that always print dim Sheetnames(1 to 3) as string ... sheetnames(1) = "default#1" sheetnames(2) = "default#2" sheetnames(3) = "default#3" 'so the rest of the code won't change sctr = ubound(sheetnames) - lbound(sheetnames) + 1 ======== If you mean that you already have some sheets added, but you MAY want to print mo 'add the "default" sheets he sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" 'then do your looping for wctr = 1 to 17 set wks = worksheets("B" & wctr) if wks.name = me.name then 'do nothing else if wks.range("b17").value < "" then ... end if next wctr =============== Since you know how many you're adding, you could replace this: 'add the "default" sheets he sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" with 'add the "default" sheets he ReDim Preserve SheetNames(1 To sCtr + 3) sctr = sctr + 1 SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 SheetNames(sCtr) = "default#2" This avoids a few "redim preserve"'s that aren't really necessary. Aaron wrote: That code is great and it works but your code and your comments have made me realize I need to change my approach. I need to change the code checking cell B71 to only check sheets "B1" through "B15". Also in the section of code you wrote on this how can I include some sheets that need printed by default. There are default sheets that should be printed in order both before and after the sheets being evaluated in for content in cell B71. Also, do you have any ideas on how to keep the sheet with all the check boxes from being printed. Thank you, Aaron "Dave Peterson" wrote: Did you test that .printout line to see if you really got a single file. If you got multiple files, then you're wasting your time with this approach! Since you're building an array of sheet names, you could check to see if the worksheet name is already in the array. Dim Res as variant 'you only need to declare wks once and you can reuse it in both loops 'you don't need sh or she dim wks as worksheet .....lots of code For Each wks In ThisWorkbook.Worksheets If wks.Visible = -1 Then If wks.Range("B71").Value < "" Then if sctr = 0 then 'no names in array yet, so just add it sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name else 'there are names in the array, so check to see if that one is 'is in the array res = application.match(wks.name, sheetnames,0) if isnumeric(res) then 'already there, so don't add it again else 'not there, so add it. sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name end if end if end if end if next wks and you'd still print with something like: If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If Aaron wrote: So I think I have it for the most part. Most of the code is for printing the report or the proposal for some financial work I do. There are 2 sections in the original code I posted where it is selecting sheets to be printed for technical review based on the content of the sheets. These are to be printed in addition to default sheets to be printed. How do I revise this to your suggested code to get the correct effect? If CheckBox31 = True Then Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut End If <<snipped -- Dave Peterson |
Print selection using check boxes
First, I never saw a response where you said that printing the array of sheets
made one file. Did you test it? Second... You can drop almost all the "redim preserve" statements. This is better: Dim SheetNames() as string ....other dim statements. redim sheetnames(1 to thisworkbook.sheets.count) You'll still keep track of the sctr stuff (sctr = SheetCounter). But at the end, your print routine changes to: if sctr 0 then 'chop the elements that weren't used redim preserve sheetnames(1 to sctr) Sheets(SheetNames).PrintOut preview:=True end if Dave Peterson wrote: The simple answer first (I think!). If the way the worksheet with the checkbox gets included is through one of those loops, you can avoid that worksheet by looking at the name. For Each wks In ThisWorkbook.Worksheets if wks.name = me.name then 'do nothing else If wks.Range("B71").Value < "" Then .... ========== If you always have worksheets named B1 to B17, you could use: dim wCtr as long 'near the top with the other Dim Statements .... for wctr = 1 to 17 set wks = worksheets("B" & wctr) if wks.name = me.name then 'do nothing else if wks.range("b17").value < "" then ... end if next wctr You won't loop through all the worksheets--you'll just look at B1 to B17. I'm not sure what you mean by the worksheets that print by default. If you mean that they always print, you could use: 'say you have 3 sheets that always print dim Sheetnames(1 to 3) as string ... sheetnames(1) = "default#1" sheetnames(2) = "default#2" sheetnames(3) = "default#3" 'so the rest of the code won't change sctr = ubound(sheetnames) - lbound(sheetnames) + 1 ======== If you mean that you already have some sheets added, but you MAY want to print mo 'add the "default" sheets he sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" 'then do your looping for wctr = 1 to 17 set wks = worksheets("B" & wctr) if wks.name = me.name then 'do nothing else if wks.range("b17").value < "" then ... end if next wctr =============== Since you know how many you're adding, you could replace this: 'add the "default" sheets he sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" with 'add the "default" sheets he ReDim Preserve SheetNames(1 To sCtr + 3) sctr = sctr + 1 SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 SheetNames(sCtr) = "default#2" This avoids a few "redim preserve"'s that aren't really necessary. Aaron wrote: That code is great and it works but your code and your comments have made me realize I need to change my approach. I need to change the code checking cell B71 to only check sheets "B1" through "B15". Also in the section of code you wrote on this how can I include some sheets that need printed by default. There are default sheets that should be printed in order both before and after the sheets being evaluated in for content in cell B71. Also, do you have any ideas on how to keep the sheet with all the check boxes from being printed. Thank you, Aaron "Dave Peterson" wrote: Did you test that .printout line to see if you really got a single file. If you got multiple files, then you're wasting your time with this approach! Since you're building an array of sheet names, you could check to see if the worksheet name is already in the array. Dim Res as variant 'you only need to declare wks once and you can reuse it in both loops 'you don't need sh or she dim wks as worksheet .....lots of code For Each wks In ThisWorkbook.Worksheets If wks.Visible = -1 Then If wks.Range("B71").Value < "" Then if sctr = 0 then 'no names in array yet, so just add it sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name else 'there are names in the array, so check to see if that one is 'is in the array res = application.match(wks.name, sheetnames,0) if isnumeric(res) then 'already there, so don't add it again else 'not there, so add it. sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name end if end if end if end if next wks and you'd still print with something like: If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If Aaron wrote: So I think I have it for the most part. Most of the code is for printing the report or the proposal for some financial work I do. There are 2 sections in the original code I posted where it is selecting sheets to be printed for technical review based on the content of the sheets. These are to be printed in addition to default sheets to be printed. How do I revise this to your suggested code to get the correct effect? If CheckBox31 = True Then Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut End If <<snipped -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
Ok. It is not quite there yet. I have attempted to implement your input but
I am not doing it correctly. It is still printing the Checkbox sheet. The code that selectively prints pages by evaluatinf cell B71 produces 4 files a sheet by itself 2 sheets together and then 3 sheets together. To anwser your other question, yes the Array prints in one file. The code as it stands now. Option Explicit Private Sub PrintSelected_Click() Dim Res As Variant Dim sCtr As Long Dim wCtr As Long Dim SheetNames() As String Dim wks As Worksheet ReDim SheetNames(1 To ThisWorkbook.Sheets.Count) Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "ClientData" End If sCtr = 0 If CheckBox2 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "W1" End If sCtr = 0 If CheckBox3 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Fee" End If sCtr = 0 If CheckBox4 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Data" End If sCtr = 0 If CheckBox5 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B1" End If sCtr = 0 If CheckBox6 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B2" End If sCtr = 0 If CheckBox7 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B3" End If sCtr = 0 If CheckBox8 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B4" End If sCtr = 0 If CheckBox9 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B5" End If sCtr = 0 If CheckBox10 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B6" End If sCtr = 0 If CheckBox11 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B7" End If sCtr = 0 If CheckBox12 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B8" End If sCtr = 0 If CheckBox13 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B9" End If sCtr = 0 If CheckBox14 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B10" End If sCtr = 0 If CheckBox15 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B11" End If sCtr = 0 If CheckBox16 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B12" End If sCtr = 0 If CheckBox17 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B13" End If sCtr = 0 If CheckBox18 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B14" End If sCtr = 0 If CheckBox19 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "B15" End If sCtr = 0 If CheckBox20 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Combined" End If sCtr = 0 If CheckBox21 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Summary" End If sCtr = 0 If CheckBox22 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Results" End If sCtr = 0 If CheckBox23 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Bar" End If sCtr = 0 If CheckBox24 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Pie" End If sCtr = 0 If CheckBox25 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Cash" End If sCtr = 0 If CheckBox26 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "NPV" End If sCtr = 0 If CheckBox27 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "ExI" End If sCtr = 0 If CheckBox30 = True Then sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "Summary" End If 'Proposal Review Print If CheckBox29 = True Then ReDim Preserve SheetNames(1 To sCtr + 5) sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" sCtr = sCtr + 1 SheetNames(sCtr) = "W1" sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" sCtr = sCtr + 1 SheetNames(sCtr) = "Data" For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) For Each wks In ThisWorkbook.Worksheets If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.Name Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.Name End If End If End If End If Next wks Next wCtr End If If sCtr 0 Then 'chop the elements that weren't used ReDim Preserve SheetNames(1 To sCtr) Sheets(SheetNames).PrintOut End If If CheckBox28 = True Then Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "ExI")).PrintOut End If End Sub "Dave Peterson" wrote: First, I never saw a response where you said that printing the array of sheets made one file. Did you test it? Second... You can drop almost all the "redim preserve" statements. This is better: Dim SheetNames() as string ....other dim statements. redim sheetnames(1 to thisworkbook.sheets.count) You'll still keep track of the sctr stuff (sctr = SheetCounter). But at the end, your print routine changes to: if sctr 0 then 'chop the elements that weren't used redim preserve sheetnames(1 to sctr) Sheets(SheetNames).PrintOut preview:=True end if Dave Peterson wrote: The simple answer first (I think!). If the way the worksheet with the checkbox gets included is through one of those loops, you can avoid that worksheet by looking at the name. For Each wks In ThisWorkbook.Worksheets if wks.name = me.name then 'do nothing else If wks.Range("B71").Value < "" Then .... ========== If you always have worksheets named B1 to B17, you could use: dim wCtr as long 'near the top with the other Dim Statements .... for wctr = 1 to 17 set wks = worksheets("B" & wctr) if wks.name = me.name then 'do nothing else if wks.range("b17").value < "" then ... end if next wctr You won't loop through all the worksheets--you'll just look at B1 to B17. I'm not sure what you mean by the worksheets that print by default. If you mean that they always print, you could use: 'say you have 3 sheets that always print dim Sheetnames(1 to 3) as string ... sheetnames(1) = "default#1" sheetnames(2) = "default#2" sheetnames(3) = "default#3" 'so the rest of the code won't change sctr = ubound(sheetnames) - lbound(sheetnames) + 1 ======== If you mean that you already have some sheets added, but you MAY want to print mo 'add the "default" sheets he sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" 'then do your looping for wctr = 1 to 17 set wks = worksheets("B" & wctr) if wks.name = me.name then 'do nothing else if wks.range("b17").value < "" then ... end if next wctr =============== Since you know how many you're adding, you could replace this: 'add the "default" sheets he sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = "default#2" with 'add the "default" sheets he ReDim Preserve SheetNames(1 To sCtr + 3) sctr = sctr + 1 SheetNames(sCtr) = "default#1" sCtr = sCtr + 1 SheetNames(sCtr) = "default#2" sCtr = sCtr + 1 SheetNames(sCtr) = "default#2" This avoids a few "redim preserve"'s that aren't really necessary. Aaron wrote: That code is great and it works but your code and your comments have made me realize I need to change my approach. I need to change the code checking cell B71 to only check sheets "B1" through "B15". Also in the section of code you wrote on this how can I include some sheets that need printed by default. There are default sheets that should be printed in order both before and after the sheets being evaluated in for content in cell B71. Also, do you have any ideas on how to keep the sheet with all the check boxes from being printed. Thank you, Aaron "Dave Peterson" wrote: Did you test that .printout line to see if you really got a single file. If you got multiple files, then you're wasting your time with this approach! Since you're building an array of sheet names, you could check to see if the worksheet name is already in the array. Dim Res as variant 'you only need to declare wks once and you can reuse it in both loops 'you don't need sh or she dim wks as worksheet .....lots of code For Each wks In ThisWorkbook.Worksheets If wks.Visible = -1 Then If wks.Range("B71").Value < "" Then if sctr = 0 then 'no names in array yet, so just add it sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name else 'there are names in the array, so check to see if that one is 'is in the array res = application.match(wks.name, sheetnames,0) if isnumeric(res) then 'already there, so don't add it again else 'not there, so add it. sCtr = sCtr + 1 ReDim Preserve SheetNames(1 To sCtr) SheetNames(sCtr) = wks.name end if end if end if end if next wks and you'd still print with something like: If sCtr 0 Then Sheets(SheetNames).PrintOut preview:=True End If Aaron wrote: So I think I have it for the most part. Most of the code is for printing the report or the proposal for some financial work I do. There are 2 sections in the original code I posted where it is selecting sheets to be printed for technical review based on the content of the sheets. These are to be printed in addition to default sheets to be printed. How do I revise this to your suggested code to get the correct effect? If CheckBox31 = True Then Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")).PrintOut 'xlSheetVisible = -1 Dim she As Worksheet For Each she In ThisWorkbook.Worksheets If she.Visible = -1 Then If she.Range("B71").Value < "" Then she.PrintOut End If End If Next she Sheets(Array("Results", "bar", "pie", "Cash", "NPV", "Ex I")).PrintOut End If <<snipped -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
I really meant that you should delete all those "redim preserve" lines. I
wasn't kidding. And you don't want to set the sheetcounter (sCtr) back to 0 all those times. And I have no idea why the sheet with the checkboxes is printing. If it's included in the SheetNames array by a single checkbox, then remove that from your code. If it's caused by looping through B1 to B15 (not 17???) Option Explicit Private Sub PrintSelected_Click() Dim Res As Variant Dim sCtr As Long Dim wCtr As Long Dim SheetNames() As String Dim wks As Worksheet ReDim SheetNames(1 To ThisWorkbook.Sheets.Count) Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" End If If CheckBox2 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "W1" End If If CheckBox3 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" End If If CheckBox4 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Data" End If If CheckBox5 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B1" End If If CheckBox6 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B2" End If If CheckBox7 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B3" End If If CheckBox8 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B4" End If If CheckBox9 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B5" End If If CheckBox10 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B6" End If If CheckBox11 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B7" End If If CheckBox12 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B8" End If If CheckBox13 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B9" End If If CheckBox14 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B10" End If If CheckBox15 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B11" End If If CheckBox16 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B12" End If If CheckBox17 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B13" End If If CheckBox18 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B14" End If If CheckBox19 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B15" End If If CheckBox20 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Combined" End If If CheckBox21 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If If CheckBox22 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Results" End If If CheckBox23 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Bar" End If If CheckBox24 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Pie" End If If CheckBox25 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Cash" End If If CheckBox26 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "NPV" End If If CheckBox27 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ExI" End If If CheckBox30 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If 'Proposal Review Print If CheckBox29 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" sCtr = sCtr + 1 SheetNames(sCtr) = "W1" sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" sCtr = sCtr + 1 SheetNames(sCtr) = "Data" For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If Next wCtr End If 'I don't understand this portion. 'you've already added results to the worksheet to be printed 'with checkbox 22 'In fact, why are checkboxes 22 to 27 used? ' If CheckBox28 = True Then ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "Results" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "bar" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "pie" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "cash" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "npv" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "exi" ' End If If sCtr 0 Then 'chop the elements that weren't used ReDim Preserve SheetNames(1 To sCtr) Sheets(SheetNames).PrintOut End If End Sub Aaron wrote: Ok. It is not quite there yet. I have attempted to implement your input but I am not doing it correctly. It is still printing the Checkbox sheet. The code that selectively prints pages by evaluatinf cell B71 produces 4 files a sheet by itself 2 sheets together and then 3 sheets together. To anwser your other question, yes the Array prints in one file. The code as it stands now. <<snipped -- Dave Peterson |
Print selection using check boxes
I have set up the print options page with 4 primary print options; Proposal,
report, proposal review and report review these print options will print the appropriate sheets. I addition every sheet has its own check box so that a single or any combination of sheets can be selcted for printing. I made the changes you suggest and now I am getting a "next without for" error. I don't see where it is coming from. Aaron "Dave Peterson" wrote: I really meant that you should delete all those "redim preserve" lines. I wasn't kidding. And you don't want to set the sheetcounter (sCtr) back to 0 all those times. And I have no idea why the sheet with the checkboxes is printing. If it's included in the SheetNames array by a single checkbox, then remove that from your code. If it's caused by looping through B1 to B15 (not 17???) Option Explicit Private Sub PrintSelected_Click() Dim Res As Variant Dim sCtr As Long Dim wCtr As Long Dim SheetNames() As String Dim wks As Worksheet ReDim SheetNames(1 To ThisWorkbook.Sheets.Count) Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" End If If CheckBox2 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "W1" End If If CheckBox3 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" End If If CheckBox4 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Data" End If If CheckBox5 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B1" End If If CheckBox6 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B2" End If If CheckBox7 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B3" End If If CheckBox8 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B4" End If If CheckBox9 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B5" End If If CheckBox10 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B6" End If If CheckBox11 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B7" End If If CheckBox12 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B8" End If If CheckBox13 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B9" End If If CheckBox14 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B10" End If If CheckBox15 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B11" End If If CheckBox16 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B12" End If If CheckBox17 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B13" End If If CheckBox18 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B14" End If If CheckBox19 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B15" End If If CheckBox20 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Combined" End If If CheckBox21 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If If CheckBox22 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Results" End If If CheckBox23 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Bar" End If If CheckBox24 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Pie" End If If CheckBox25 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Cash" End If If CheckBox26 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "NPV" End If If CheckBox27 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ExI" End If If CheckBox30 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If 'Proposal Review Print If CheckBox29 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" sCtr = sCtr + 1 SheetNames(sCtr) = "W1" sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" sCtr = sCtr + 1 SheetNames(sCtr) = "Data" For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If Next wCtr End If 'I don't understand this portion. 'you've already added results to the worksheet to be printed 'with checkbox 22 'In fact, why are checkboxes 22 to 27 used? ' If CheckBox28 = True Then ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "Results" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "bar" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "pie" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "cash" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "npv" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "exi" ' End If If sCtr 0 Then 'chop the elements that weren't used ReDim Preserve SheetNames(1 To sCtr) Sheets(SheetNames).PrintOut End If End Sub Aaron wrote: Ok. It is not quite there yet. I have attempted to implement your input but I am not doing it correctly. It is still printing the Checkbox sheet. The code that selectively prints pages by evaluatinf cell B71 produces 4 files a sheet by itself 2 sheets together and then 3 sheets together. To anwser your other question, yes the Array prints in one file. The code as it stands now. <<snipped -- Dave Peterson |
Print selection using check boxes
I cleaned up too much stuff:
For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If End If Next wCtr Aaron wrote: I have set up the print options page with 4 primary print options; Proposal, report, proposal review and report review these print options will print the appropriate sheets. I addition every sheet has its own check box so that a single or any combination of sheets can be selcted for printing. I made the changes you suggest and now I am getting a "next without for" error. I don't see where it is coming from. Aaron "Dave Peterson" wrote: I really meant that you should delete all those "redim preserve" lines. I wasn't kidding. And you don't want to set the sheetcounter (sCtr) back to 0 all those times. And I have no idea why the sheet with the checkboxes is printing. If it's included in the SheetNames array by a single checkbox, then remove that from your code. If it's caused by looping through B1 to B15 (not 17???) Option Explicit Private Sub PrintSelected_Click() Dim Res As Variant Dim sCtr As Long Dim wCtr As Long Dim SheetNames() As String Dim wks As Worksheet ReDim SheetNames(1 To ThisWorkbook.Sheets.Count) Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" End If If CheckBox2 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "W1" End If If CheckBox3 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" End If If CheckBox4 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Data" End If If CheckBox5 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B1" End If If CheckBox6 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B2" End If If CheckBox7 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B3" End If If CheckBox8 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B4" End If If CheckBox9 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B5" End If If CheckBox10 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B6" End If If CheckBox11 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B7" End If If CheckBox12 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B8" End If If CheckBox13 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B9" End If If CheckBox14 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B10" End If If CheckBox15 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B11" End If If CheckBox16 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B12" End If If CheckBox17 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B13" End If If CheckBox18 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B14" End If If CheckBox19 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B15" End If If CheckBox20 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Combined" End If If CheckBox21 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If If CheckBox22 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Results" End If If CheckBox23 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Bar" End If If CheckBox24 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Pie" End If If CheckBox25 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Cash" End If If CheckBox26 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "NPV" End If If CheckBox27 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ExI" End If If CheckBox30 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If 'Proposal Review Print If CheckBox29 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" sCtr = sCtr + 1 SheetNames(sCtr) = "W1" sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" sCtr = sCtr + 1 SheetNames(sCtr) = "Data" For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If Next wCtr End If 'I don't understand this portion. 'you've already added results to the worksheet to be printed 'with checkbox 22 'In fact, why are checkboxes 22 to 27 used? ' If CheckBox28 = True Then ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "Results" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "bar" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "pie" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "cash" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "npv" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "exi" ' End If If sCtr 0 Then 'chop the elements that weren't used ReDim Preserve SheetNames(1 To sCtr) Sheets(SheetNames).PrintOut End If End Sub Aaron wrote: Ok. It is not quite there yet. I have attempted to implement your input but I am not doing it correctly. It is still printing the Checkbox sheet. The code that selectively prints pages by evaluatinf cell B71 produces 4 files a sheet by itself 2 sheets together and then 3 sheets together. To anwser your other question, yes the Array prints in one file. The code as it stands now. <<snipped -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
Hi Dave,
Thanks for all your help! It is "working" and not throwing off any errors. It is still printing the Checkbox page. Also for check box 29 it is exhibiting some odd results. Let's say the code determines 7 sheets need to be printed. It is the correct 7 sheets but it will produce a print job that is in 3 files; one sheet in one file, 2 in the next, and 4 in the last. I came across one of your old posts. You really seem to be thee guy when it come to cusotm print set ups. Do you think this would apply to not printing the check box page? Dim FirstSheet as boolean FirstSheet = true If PhaseCheckBox.Value = True Then sheets("131 Phase1").select firstsheet firstsheet = false sheets("131 Phase1").select firstsheet end if if shopcheckbox.value = true then sheets("131 shop1").select firstsheet firstsheet = false sheets("131 shop2").select firstsheet end if if rfcheckbox.value = true then sheets("Rf Sheet").select firstsheet firstsheet = false end if if statuscheckbox.value = true then sheets("Status").select firstsheet firstsheet = false end if if firstsheet = true then 'never changed to false, so no sheets were chosen msgbox "No sheets selected" else ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True end if "Dave Peterson" wrote: I cleaned up too much stuff: For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If End If Next wCtr Aaron wrote: I have set up the print options page with 4 primary print options; Proposal, report, proposal review and report review these print options will print the appropriate sheets. I addition every sheet has its own check box so that a single or any combination of sheets can be selcted for printing. I made the changes you suggest and now I am getting a "next without for" error. I don't see where it is coming from. Aaron "Dave Peterson" wrote: I really meant that you should delete all those "redim preserve" lines. I wasn't kidding. And you don't want to set the sheetcounter (sCtr) back to 0 all those times. And I have no idea why the sheet with the checkboxes is printing. If it's included in the SheetNames array by a single checkbox, then remove that from your code. If it's caused by looping through B1 to B15 (not 17???) Option Explicit Private Sub PrintSelected_Click() Dim Res As Variant Dim sCtr As Long Dim wCtr As Long Dim SheetNames() As String Dim wks As Worksheet ReDim SheetNames(1 To ThisWorkbook.Sheets.Count) Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" End If If CheckBox2 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "W1" End If If CheckBox3 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" End If If CheckBox4 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Data" End If If CheckBox5 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B1" End If If CheckBox6 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B2" End If If CheckBox7 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B3" End If If CheckBox8 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B4" End If If CheckBox9 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B5" End If If CheckBox10 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B6" End If If CheckBox11 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B7" End If If CheckBox12 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B8" End If If CheckBox13 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B9" End If If CheckBox14 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B10" End If If CheckBox15 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B11" End If If CheckBox16 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B12" End If If CheckBox17 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B13" End If If CheckBox18 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B14" End If If CheckBox19 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B15" End If If CheckBox20 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Combined" End If If CheckBox21 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If If CheckBox22 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Results" End If If CheckBox23 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Bar" End If If CheckBox24 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Pie" End If If CheckBox25 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Cash" End If If CheckBox26 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "NPV" End If If CheckBox27 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ExI" End If If CheckBox30 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If 'Proposal Review Print If CheckBox29 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" sCtr = sCtr + 1 SheetNames(sCtr) = "W1" sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" sCtr = sCtr + 1 SheetNames(sCtr) = "Data" For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If Next wCtr End If 'I don't understand this portion. 'you've already added results to the worksheet to be printed 'with checkbox 22 'In fact, why are checkboxes 22 to 27 used? ' If CheckBox28 = True Then ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "Results" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "bar" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "pie" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "cash" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "npv" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "exi" ' End If If sCtr 0 Then 'chop the elements that weren't used ReDim Preserve SheetNames(1 To sCtr) Sheets(SheetNames).PrintOut End If End Sub Aaron wrote: Ok. It is not quite there yet. I have attempted to implement your input but I am not doing it correctly. It is still printing the Checkbox sheet. The code that selectively prints pages by evaluatinf cell B71 produces 4 files a sheet by itself 2 sheets together and then 3 sheets together. To anwser your other question, yes the Array prints in one file. The code as it stands now. <<snipped -- Dave Peterson -- Dave Peterson |
Print selection using check boxes
What is the name of the sheet with the checkboxes?
What portion of the code adds this name to the SheetNames array? Since you're printing the array of sheetnames with one .printout statement, I don't think it's an excel problem (or fix). This is just a guess, but is there a difference between those sheets and the rest--landscape vs portrait maybe. I'd look at the pdf program. And no, that code to print selected sheets won't be of any help. You could redesign your code so that it selects the sheets instead of printing, then prints the selected sheets. But I wouldn't think that it would help. Maybe you can test how many files are created by selecting those sheets (manually), then printing. Aaron wrote: Hi Dave, Thanks for all your help! It is "working" and not throwing off any errors. It is still printing the Checkbox page. Also for check box 29 it is exhibiting some odd results. Let's say the code determines 7 sheets need to be printed. It is the correct 7 sheets but it will produce a print job that is in 3 files; one sheet in one file, 2 in the next, and 4 in the last. I came across one of your old posts. You really seem to be thee guy when it come to cusotm print set ups. Do you think this would apply to not printing the check box page? Dim FirstSheet as boolean FirstSheet = true If PhaseCheckBox.Value = True Then sheets("131 Phase1").select firstsheet firstsheet = false sheets("131 Phase1").select firstsheet end if if shopcheckbox.value = true then sheets("131 shop1").select firstsheet firstsheet = false sheets("131 shop2").select firstsheet end if if rfcheckbox.value = true then sheets("Rf Sheet").select firstsheet firstsheet = false end if if statuscheckbox.value = true then sheets("Status").select firstsheet firstsheet = false end if if firstsheet = true then 'never changed to false, so no sheets were chosen msgbox "No sheets selected" else ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True end if "Dave Peterson" wrote: I cleaned up too much stuff: For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If End If Next wCtr Aaron wrote: I have set up the print options page with 4 primary print options; Proposal, report, proposal review and report review these print options will print the appropriate sheets. I addition every sheet has its own check box so that a single or any combination of sheets can be selcted for printing. I made the changes you suggest and now I am getting a "next without for" error. I don't see where it is coming from. Aaron "Dave Peterson" wrote: I really meant that you should delete all those "redim preserve" lines. I wasn't kidding. And you don't want to set the sheetcounter (sCtr) back to 0 all those times. And I have no idea why the sheet with the checkboxes is printing. If it's included in the SheetNames array by a single checkbox, then remove that from your code. If it's caused by looping through B1 to B15 (not 17???) Option Explicit Private Sub PrintSelected_Click() Dim Res As Variant Dim sCtr As Long Dim wCtr As Long Dim SheetNames() As String Dim wks As Worksheet ReDim SheetNames(1 To ThisWorkbook.Sheets.Count) Application.Dialogs(xlDialogPrint).Show sCtr = 0 If CheckBox1 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" End If If CheckBox2 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "W1" End If If CheckBox3 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" End If If CheckBox4 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Data" End If If CheckBox5 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B1" End If If CheckBox6 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B2" End If If CheckBox7 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B3" End If If CheckBox8 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B4" End If If CheckBox9 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B5" End If If CheckBox10 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B6" End If If CheckBox11 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B7" End If If CheckBox12 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B8" End If If CheckBox13 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B9" End If If CheckBox14 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B10" End If If CheckBox15 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B11" End If If CheckBox16 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B12" End If If CheckBox17 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B13" End If If CheckBox18 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B14" End If If CheckBox19 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "B15" End If If CheckBox20 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Combined" End If If CheckBox21 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If If CheckBox22 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Results" End If If CheckBox23 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Bar" End If If CheckBox24 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Pie" End If If CheckBox25 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Cash" End If If CheckBox26 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "NPV" End If If CheckBox27 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "ExI" End If If CheckBox30 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" End If 'Proposal Review Print If CheckBox29 = True Then sCtr = sCtr + 1 SheetNames(sCtr) = "Summary" sCtr = sCtr + 1 SheetNames(sCtr) = "ClientData" sCtr = sCtr + 1 SheetNames(sCtr) = "W1" sCtr = sCtr + 1 SheetNames(sCtr) = "Fee" sCtr = sCtr + 1 SheetNames(sCtr) = "Data" For wCtr = 1 To 15 Set wks = Worksheets("B" & wCtr) If wks.Name = Me.Name Then 'do nothing Else If wks.Range("B71").Value < "" Then If sCtr = 0 Then 'no names in array yet, so just add it sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name Else 'there are names in the array, so check to see if that one is 'is in the array Res = Application.Match(wks.Name, SheetNames, 0) If IsNumeric(Res) Then 'already there, so don't add it again Else 'not there, so add it. sCtr = sCtr + 1 SheetNames(sCtr) = wks.Name End If End If Next wCtr End If 'I don't understand this portion. 'you've already added results to the worksheet to be printed 'with checkbox 22 'In fact, why are checkboxes 22 to 27 used? ' If CheckBox28 = True Then ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "Results" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "bar" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "pie" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "cash" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "npv" ' sCtr = sCtr + 1 ' SheetNames(sCtr) = "exi" ' End If If sCtr 0 Then 'chop the elements that weren't used ReDim Preserve SheetNames(1 To sCtr) Sheets(SheetNames).PrintOut End If End Sub Aaron wrote: Ok. It is not quite there yet. I have attempted to implement your input but I am not doing it correctly. It is still printing the Checkbox sheet. The code that selectively prints pages by evaluatinf cell B71 produces 4 files a sheet by itself 2 sheets together and then 3 sheets together. To anwser your other question, yes the Array prints in one file. The code as it stands now. <<snipped -- Dave Peterson -- Dave Peterson -- Dave Peterson |
| All times are GMT +1. The time now is 12:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com