End without Next Error

A

Aaron

Check boxes to select what combination of sheets is printed.

I am getting an End without Next Error. Help!

Private Sub PrintSelected_Click()
Application.Dialogs(xlDialogPrint).Show

If CheckBox1 = True Then
Sheets("ClientData").PrintOut
If CheckBox2 = True Then
Sheets("W1").PrintOut
If CheckBox3 = True Then
Sheets("Fee").PrintOut
If CheckBox4 = True Then
Sheets("Data").PrintOut
If CheckBox5 = True Then
Sheets("B1").PrintOut
If CheckBox6 = True Then
Sheets("B2").PrintOut
If CheckBox7 = True Then
Sheets("B3").PrintOut
If CheckBox8 = True Then
Sheets("B4").PrintOut
If CheckBox9 = True Then
Sheets("B5").PrintOut
If CheckBox10 = True Then
Sheets("B6").PrintOut
If CheckBox11 = True Then
Sheets("B7").PrintOut
If CheckBox12 = True Then
Sheets("B8").PrintOut
If CheckBox13 = True Then
Sheets("B9").PrintOut
If CheckBox14 = True Then
Sheets("B10").PrintOut
If CheckBox15 = True Then
Sheets("B11").PrintOut
If CheckBox16 = True Then
Sheets("B12").PrintOut
If CheckBox17 = True Then
Sheets("B13").PrintOut
If CheckBox18 = True Then
Sheets("B14").PrintOut
If CheckBox19 = True Then
Sheets("B15").PrintOut
If CheckBox20 = True Then
Sheets("Combined").PrintOut
If CheckBox21 = True Then
Sheets("Summary").PrintOut
If CheckBox22 = True Then
Sheets("Results").PrintOut
If CheckBox23 = True Then
Sheets("Bar").PrintOut
If CheckBox24 = True Then
Sheets("Pie").PrintOut
If CheckBox25 = True Then
Sheets("Cash").PrintOut
If CheckBox26 = True Then
Sheets("NPV").PrintOut
If CheckBox27 = True Then
Sheets("EX I").PrintOut
If CheckBox30 = True Then
Sheets("Summary").PrintOut

If CheckBox28 = True Then
Sheets("Results").PrintOut
Sheets("bar").PrintOut
Sheets("pie").PrintOut
Sheets("Cash").PrintOut
Sheets("NPV").PrintOut
Sheets("Ex I").PrintOut

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

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

Sheets("Results").PrintOut
Sheets("bar").PrintOut
Sheets("pie").PrintOut
Sheets("Cash").PrintOut
Sheets("NPV").PrintOut
Sheets("Ex I").PrintOut


End If
End If


End Sub
 
B

Barb Reinhardt

Every time you do this

If CheckBox1 = True Then
Sheets("ClientData").PrintOut

You need End If at the end.


Otherwise, do it this way

If CheckBox1 = True Then _ '(note the space and an underbar)
Sheets("ClientData").PrintOut
 
R

Rick Rothstein

To add to Barb's reply, when you did this...

If CheckBox29 = True Then
Sheets("Summary").PrintOut
Sheets("ClientData").PrintOut
Sheets("W1").PrintOut
Sheets("Fee").PrintOut
Sheets("Data").PrintOut

I assume the 5 PrintOut commands were intended to run only if ChectkBox29 is
true. To do that, you must have an End If statement at the after the last
statement being controlled by the If..Then statement. So, you need to write
it like this (note the indenting to make it clearer what is supposed to be
included in the If..Then block of code)...

If CheckBox29 = True Then
Sheets("Summary").PrintOut
Sheets("ClientData").PrintOut
Sheets("W1").PrintOut
Sheets("Fee").PrintOut
Sheets("Data").PrintOut
End If

Wherever you have multiple statements to be executed for a given If..Then
statement, think of the If..Then statement and the End If statement as if
they were opening and closing parentheses... they enclose whatever is meant
to be grouped together... you can't have one without the other. The one
exception to the "If you have If..Then, then you need End If" rule is if you
only have one statement to execute if the If..Then is true... VB allows you
to put that statement on the **same** line as the If..Then statement and, if
you do that, omit the End If statement. So, the above code, with the End If
statement, is required as shown above because there is more than one
statement being controlled by the If..Then statement. However, for a
**single** controlled statement, either of these two formats are acceptable

If <logical test> Then <SingleStatment>

or

If <logical test> Then
<SingleStatment>
End If

By the way, the format Barb gave you, using the above pseudo-code...

If <logical test> Then _
<SingleStatment>

is equivalent to the first single line statement above... the space followed
by an underbar is VB's way of allowing you to split a single statement
across two or more lines for readability purposes (you don't have to keep
scrolling left to see what is at the end of your line of code)... it is
**not** a violation of the required End If statement because while written
like this...

If <logical test> Then _
<SingleStatment>

VB actually sees it like this...

If <logical test> Then <SingleStatment>

The first format (using the line continuation) is simply a display
convenience VB allows for the human reader of the code.
 
A

Aaron

Thank you!

Now the error is "End if Without Block if"

Here is the revised code:

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

Sheets("Results").PrintOut
Sheets("bar").PrintOut
Sheets("pie").PrintOut
Sheets("Cash").PrintOut
Sheets("NPV").PrintOut
Sheets("Ex I").PrintOut
End If
End If
End If


End Sub
 
B

Barb Reinhardt

You have 2 places with

End If
End If
End If

You only need one for both of them.
 
B

Barb Reinhardt

I'd recommend that you add some indents to your code so you can see what's
going on. Here's a modified version. You had a couple of For/Next loops
without the Next. I take back the End If comment.

You may want to rearrange some of this code because I'm not sure what you
want to do in your For/Next

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
 
R

Rick Rothstein

The best thing you can possibly do for yourself at this stage of your
programming development is to learn how to indent related blocks of code
(that is, the code between If..Then and End If, For and Next, Do and Loop,
Select Case and End Select, etc.)... it will help you see when you have
forgotten something. Here is one of the two sections of code that
incorporate a For loop that you posted...

' ********** Start Of Your Code **********
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
End If
' ********** End Of Your Code **********

Now here is how I would have written it...

' ********** Start Of My Code **********
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
End If
' ********** End Of Your Code **********

Take a look at the difference in indenting between the last two lines... it
is larger than the rest of the line-to-line indenting. That pretty much
tells you that something is missing. Following down from the beginning of
each indented line shows the For statement does not have its Next statement
companion statement... omitting that Next statement will cause an error. You
repeated that omission in your other For loop as well (it too is missing the
Next statement). Good indenting technique can be an aid in debugging
problems and is an habit you should get into... it is definitely worth the
effort (and that effort is minimal for sure).

One closing thought... just like I told you to consider If..Then and End If
as if they were opening and closing parentheses, both being needed to
properly group the items they enclosed... you should think the same way
about For..Next, Do..Loop, Select Case..End Select, Open..Close and any
other block statement pairs that can group (enclose) multiple statements...
the pairing is required and indenting the grouped statements can help in
identifying when you failed to group pair them up.
 
R

Rick Rothstein

And just one more thing to say about indenting... not only will you find it
useful when you are developing your code for the first time, but you will
absolutely thank yourself for having done so if you ever have to come back
in the future to modify your code... it will be much more readable which
will make your editing task much simpler to perform.
 
A

Aaron

Thank you so much! I work with VBA so infeaquently I miss stuff when I am
writing code from sctartch (vs copying it from a post.)
 
A

Aaron

Thank you for the tips. It makes sense.

I am testing it and for some reason it is printing the "Print" sheet where
the buttons and check boxes are located. I do not want it to do this. Any
ideas?
 
A

Aaron

Also, it is printing each page separately. So if I print to PDF instead of
having one file I have multiple files, one for each sheet.

Any ideas?
 
G

Gary Keramidas

try this:

If CheckBox29 = True Then
With Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data"))
..PrintOut
End With
End If
 
O

Orion Cochrane

For IF statements like you have there, you could stick a space and underscore
( _) after Then for each IF statement and you won't have to write End If for
each statement. This only works if your condition is one command line long.
Use this when the condition won't fit on the same line as your IF statement.
 

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

Print selection using check boxes 17
error VBA next without for 1
Code execution error 6
Code to Create Chart 0
Macro to print several worksheets does not work! 1
For Each Next... what's wrong with my code??? 4
VB Code help 1
EXCEL hangs on PIVOT table!! 0

Top