Is it possible to create an If statement that includes a command button (e.g. the OK button).
Is it possible to create an If statement that includes a command button (e.g. the OK button).
For what purpose, to rest if it was pressed, to call its Click event, or what?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Yeh for when it is clicked. So something like If OK = Clicked Then do something
Then just add your do something to the button's click event, the system monitors when it is clicked.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
This will not work as the procedure is too long in the button's click event. Which is why I want to make another 'Sub' but with If statements saying: If you click OK then do this
What does length have to do with it, if it is a procedure it can be called.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Basically in my Private Sub OK_Click() I have a really long If statement, so long that I now have the error saying 'Procedure too large'. I now want to split my If statement up into different subs to resolve this error. However, the If statement is only encountered when the user clicks the OK button.
I have already shown you how to make that a lot shorter.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Yes thank you, I have made it a lot shorter than it originally was, however there is still so much code left to input, it is inevitable that I am to hit the same 'Procedure too large' error again. Which is why I want to know if it is possible to include a command button in an if statement.
Rather than a command button, simply call another procedure
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
I've tried calling another procedure but then it doesn't work
Not having access to your code or workbook doesn't help but I'm very confident that a solution could be reached
Remember To Do the Following....
Use [Code].... [/Code] tags when posting code to the thread.
Mark your thread as Solved if satisfied by using the Thread Tools options.
If posting the same issue to another forum please show the link
Every very large procedure will benefit from being factored into many small sub procedures and functions. Any Sub that generates a "Procedure too large" error is by definition a very large procedure.
Anecdotally, I have written Excel applications that had over 3 Mb of code , but I have never generated a "Procedure too large" error.
It might benefit you to use what I call "Section Heads" comments in your code. For example, suppose I have to retrieve a bunc of data from a couple of Worksheets, do something with the data, then put the results back into a worksheet. Assume that all these processes take quite a bit of code.
In your situation, each of those Section Head comments could be a place to use a different (subservient) Procedure or Function.Sub Whatever(0 Dim a bunch Dim a bunch Dim a bunch Dim a bunch ''''Get Data From Sheet1 Code here Code here Code here ''''Get Data From Sheet2 Code here Code here Code here ''''Process Data Code here Code here Code here ''''Write Results to Sheet3 Code here Code here Code here End Sub
I expect the student to do their homework and find all the errrors I leeve in.
Please take the time to read the Forum FAQ
OK thanks SamT I'll give it a go.
And yeh thanks Aussiebear, majority of my code is below and hopefully a solution can be found:
Private Sub OK_Click() Unload Userform1 Worksheets("Data").Cells(1, 1).Select 'Worksheets("Data").Cells(2, 1). = ActiveCell Do While ActiveCell.Value <> Empty rowNr = rowNr + 1 ActiveCell.Offset(1, 0).Select Loop 'MsgBox rowNr rowNr = rowNr + 1 Worksheets("Data").Cells(2, 3) = CheckBox1.Value Worksheets("Data").Cells(3, 3) = CheckBox2.Value Worksheets("Data").Cells(4, 3) = CheckBox3.Value Worksheets("Data").Cells(5, 3) = CheckBox4.Value Worksheets("Data").Cells(6, 3) = CheckBox5.Value Worksheets("Data").Cells(7, 3) = CheckBox6.Value Worksheets("Data").Cells(8, 3) = CheckBox7.Value Worksheets("Data").Cells(9, 3) = CheckBox8.Value Worksheets("Data").Cells(10, 3) = CheckBox9.Value Worksheets("Data").Cells(11, 3) = CheckBox10.Value If CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-40" ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-39" Rows(1).Rows(52).Delete ElseIf CheckBox1 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1,3-40" Rows(1).Rows(4).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-2,4-40" Rows(1).Rows(5).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-3,5-40" Rows(1).Rows(6).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-4,6-40" Rows(1).Rows(7).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-5,7-40" Rows(1).Rows(8).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-6,8-40" Rows(1).Rows(9).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-7,9-40" Rows(1).Rows(10).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-8,10-40" Rows(1).Rows(11).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-9,11-40" Rows(1).Rows(12).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-10,12-40" Rows(1).Rows(13).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-11,13-40" Rows(1).Rows(14).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-12,14-40" Rows(1).Rows(15).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-13,15-40" Rows(1).Rows(16).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-14,16-40" Rows(1).Rows(17).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-15,17-40" Rows(1).Rows(18).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-16,18-40" Rows(1).Rows(19).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-17,19-40" Rows(1).Rows(20).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-18,20-40" Rows(1).Rows(21).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-19,21-40" Rows(1).Rows(32).Delete Rows(1).Rows(31).Delete Rows(1).Rows(30).Delete Rows(1).Rows(29).Delete Rows(1).Rows(28).Delete Rows(1).Rows(27).Delete Rows(1).Rows(26).Delete Rows(1).Rows(25).Delete Rows(1).Rows(24).Delete Rows(1).Rows(23).Delete Rows(1).Rows(22).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-20,22-40" Rows(1).Rows(33).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-21,23-40" Rows(1).Rows(34).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-22,24-40" Rows(1).Rows(35).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-23,25-40" Rows(1).Rows(36).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-24,26-40" Rows(1).Rows(37).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-25,27-40" Rows(1).Rows(38).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-26,28-40" Rows(1).Rows(39).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-27,29-40" Rows(1).Rows(40).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-28,30-40" Rows(1).Rows(41).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-29,31-40" Rows(1).Rows(42).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-30,32-40" Rows(1).Rows(43).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-31,33-40" Rows(1).Rows(44).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-32,34-40" Rows(1).Rows(45).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-33,35-40" Rows(1).Rows(46).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-34,36-40" Rows(1).Rows(47).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-35,37-40" Rows(1).Rows(48).Delete End If End Sub
That is a horrible procedure and makes me think that your design concept is flawed, but it doesn't look that big to me. Is this saying Procedure too large?
BTW, if you think that statements like
will delete rows 1-10 inclusive you are wrong, it just deletes row 10, so you can ditch the Rows(1). part (that will shorten it a bit).Rows(1).Rows(10).Delete
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
As most of the code is in checing those checkbox values, you could load all of your checkboxes into an array at the start (is it a form), the array index being the checkbox number, and then have a function that is passed an array of checkbox ids which are checked to return true or false, so your code would then look like
or even pass just the one not being checked.ElseIf TestCBS(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40) Then Sheets("Template").Copy ActiveSheet.Name = "TC1-29,31-40" Rows(1).Rows(42).Delete ElseIf TestCBS(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 32, 33, 34, 35, 36, 37, 38, 39, 40) Then Sheets("Template").Copy ActiveSheet.Name = "TC1-30,32-40" Rows(1).Rows(43).Delete ElseIf TestCBS(1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 33, 34, 35, 36, 37, 38, 39, 40) Then Sheets("Template").Copy ActiveSheet.Name = "TC1-31,33-40" Rows(1).Rows(44).Delete
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Yes it is saying procedure too large, and I know that it only deletes row 10, that is what I want.
Also yes it is a Userform, and even with that shortened down code I will get a 'Procedure Too Large' error again as I have much more code still to enter.
Then you need to split it up, you have been given enough advice on how to do it.
Personally, I think you need to re-design it, your concept is flawed.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
Ok thanks. I'm not the greatest at understanding VBA and I have only just begun really, but I have tried splitting it up, however for some reason it doesn't work. And I believe it is because I am not putting the code in the 'OK_Click()' bit. So I am trying to split it up and create an IF statement with the OK_Click bit in it. So is it possible to put something like this (I'll do the CheckBox array thing you advised later):
Do I have to put 'Dim a bunch' at the beginning of this like Sam T suggested? Or something else for it to work??Private Sub OK_Click() Unload Userform1 Worksheets("Data").Cells(1, 1).Select 'Worksheets("Data").Cells(2, 1). = ActiveCell Do While ActiveCell.Value <> Empty rowNr = rowNr + 1 ActiveCell.Offset(1, 0).Select Loop 'MsgBox rowNr rowNr = rowNr + 1 Worksheets("Data").Cells(2, 3) = CheckBox1.Value Worksheets("Data").Cells(3, 3) = CheckBox2.Value Worksheets("Data").Cells(4, 3) = CheckBox3.Value Worksheets("Data").Cells(5, 3) = CheckBox4.Value Worksheets("Data").Cells(6, 3) = CheckBox5.Value Worksheets("Data").Cells(7, 3) = CheckBox6.Value Worksheets("Data").Cells(8, 3) = CheckBox7.Value Worksheets("Data").Cells(9, 3) = CheckBox8.Value Worksheets("Data").Cells(10, 3) = CheckBox9.Value Worksheets("Data").Cells(11, 3) = CheckBox10.Value If CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-40" End If Sub OK1() ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-39" Rows(1).Rows(52).Delete End If End Sub
You can't embed the sub within another sub, create them as distinct entities, something like this
Your code will do more checking than the current does (or would do if it worked), but that is a price you pay for this approach.Private Sub OK_Click() Unload Userform1 Worksheets("Data").Cells(1, 1).Select 'Worksheets("Data").Cells(2, 1). = ActiveCell Do While ActiveCell.Value <> Empty rowNr = rowNr + 1 ActiveCell.Offset(1, 0).Select Loop 'MsgBox rowNr rowNr = rowNr + 1 Worksheets("Data").Cells(2, 3) = CheckBox1.Value Worksheets("Data").Cells(3, 3) = CheckBox2.Value Worksheets("Data").Cells(4, 3) = CheckBox3.Value Worksheets("Data").Cells(5, 3) = CheckBox4.Value Worksheets("Data").Cells(6, 3) = CheckBox5.Value Worksheets("Data").Cells(7, 3) = CheckBox6.Value Worksheets("Data").Cells(8, 3) = CheckBox7.Value Worksheets("Data").Cells(9, 3) = CheckBox8.Value Worksheets("Data").Cells(10, 3) = CheckBox9.Value Worksheets("Data").Cells(11, 3) = CheckBox10.Value Call Sheets40and39 Call Sheets3_40and4_40 'more calls End Sub Private Sub Sheets40and39() If CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-40" ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-39" Rows(1).Rows(52).Delete End If End Sub Private Sub Sheets3_40and4_40() If CheckBox1 = True And CheckBox3 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1,3-40" Rows(1).Rows(4).Delete ElseIf CheckBox1 = True And CheckBox2 = True And CheckBox4 = True And CheckBox5 = True And CheckBox6 = True And CheckBox7 = True And CheckBox8 = True And CheckBox9 = True And CheckBox10 = True And CheckBox11 = True And CheckBox12 = True And CheckBox13 = True And CheckBox14 = True And CheckBox15 = True And CheckBox16 = True And CheckBox17 = True And CheckBox18 = True And CheckBox19 = True And CheckBox20 = True And CheckBox21 = True And CheckBox22 = True And CheckBox23 = True And CheckBox24 = True And CheckBox25 = True And CheckBox26 = True And CheckBox27 = True And CheckBox28 = True And CheckBox29 = True And CheckBox30 = True And CheckBox31 = True And CheckBox32 = True And CheckBox33 = True And CheckBox34 = True And CheckBox35 = True And CheckBox36 = True And CheckBox37 = True And CheckBox38 = True And CheckBox39 = True And CheckBox40 = True Then Sheets("Template").Copy ActiveSheet.Name = "TC1-2,4-40" Rows(1).Rows(5).Delete End If End Sub 'etc
You could include more checks in each called sub, I just showed the approach.
I still think you should rethink your approach, I am sure you will get caught at the next hurdle as well.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber