Print selection using check boxes

A

Aaron

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
 
A

Aaron

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?
 
A

Aaron

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?
 
D

Dave Peterson

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.
 
D

Dave Peterson

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
 
A

Aaron

Thanks! I will work on it. I understand the bordom I used Excel to write
the code the first time.

Dave Peterson said:
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
Click to expand...
 
A

Aaron

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 said:
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
Click to expand...
 
D

Dave Peterson

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:

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
Click to expand...
<<snipped>>
 
A

Aaron

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
 
D

Dave Peterson

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
more:


'add the "default" sheets here:
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 here:
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 here:
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.
 
D

Dave Peterson

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
 
A

Aaron

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 said:
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
Click to expand...
 
D

Dave Peterson

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

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.
Click to expand...
<<snipped>>
 
A

Aaron

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
 
D

Dave Peterson

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
 
A

Aaron

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
 
D

Dave Peterson

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.


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
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

End without Next Error 12
Code to Create Chart 0
Code execution error 6
different results when using F5 and F8 in VBE 0
Problem Using If/Then Statements To Hide Columns 1
Macro to print several worksheets does not work! 1
Edit VBA to print Directory including file owner into Excel Spread 8
Repeat the macro using different ranges 1

Top