Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Print selection using check boxes
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 287
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 287
Default 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
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 3,355
Default 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
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 287
Default 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
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 287
Default 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

Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 287
Default 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

Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default 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
Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 287
Default 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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can't check selection boxes LA Excel Discussion (Misc queries) 2 February 2nd 10 06:46 PM
Copy and move check box (check boxes) with new cell link? Marty Excel Worksheet Functions 1 January 20th 10 07:43 PM
How do I use check boxes to determine which worksheets to print? Slugger Excel Programming 8 December 5th 07 01:24 AM
Print = moving check boxes [email protected] Excel Discussion (Misc queries) 3 August 17th 07 02:07 AM
Check boxes I have inserted in Excel move when I print? Mechelle C Excel Discussion (Misc queries) 1 June 22nd 06 06:44 PM


All times are GMT +1. The time now is 08:11 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement