| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
End without Next Error
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
You have 2 places with
End If End If End If You only need one for both of them. -- HTH, Barb Reinhardt "Aaron" wrote: 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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. -- Rick (MVP - Excel) "Aaron" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Aaron" wrote in message ... 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 "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Barb Reinhardt" wrote in message ... 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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.) "Barb Reinhardt" wrote: 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 -- HTH, Barb Reinhardt "Aaron" wrote: 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 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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? "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Aaron" wrote in message ... 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 "Rick Rothstein" wrote: 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 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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? "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Aaron" wrote in message ... 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 "Rick Rothstein" wrote: 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 |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
try this:
If CheckBox29 = True Then With Sheets(Array("Summary", "ClientData", "W1", "Fee", "Data")) ..PrintOut End With End If -- Gary "Aaron" wrote in message ... 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? "Rick Rothstein" wrote: 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. -- Rick (MVP - Excel) "Rick Rothstein" wrote in message ... 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. -- Rick (MVP - Excel) "Aaron" wrote in message ... 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 "Rick Rothstein" wrote: 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 |
|
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
End without Next Error
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. -- I am running on Excel 2003, unless otherwise stated. Please rate posts so we know when we have answered your questions. Thanks. "Aaron" wrote: 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| run time error 1004 general odbc error excel 2003 vba | Excel Programming | |||
| Error handling error # 1004 Run-time error | Excel Programming | |||
| Error Handling - On Error GoTo doesn't trap error successfully | Excel Programming | |||
| run-time error '1004': Application-defined or object-deifined error | Excel Programming | |||
| Automation Error, Unknown Error. Error value - 440 | Excel Programming | |||
Linear Mode
