Consulting

Page 1 of 3 1 2 3 LastLast
Results 1 to 20 of 45

Thread: If statement with CommandButton

  1. #1
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location

    If statement with CommandButton

    Is it possible to create an If statement that includes a command button (e.g. the OK button).

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

  3. #3
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    Yeh for when it is clicked. So something like If OK = Clicked Then do something

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

  5. #5
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    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

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

  7. #7
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    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.

  8. #8
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

  9. #9
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    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.

  10. #10
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    Rather than a command button, simply call another procedure
    Remember To Do the Following....
    Use 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

  11. #11
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    I've tried calling another procedure but then it doesn't work

  12. #12
    Moderator VBAX Guru Aussiebear's Avatar
    Joined
    Dec 2005
    Location
    Queensland
    Posts
    3,837
    Location
    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 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

  13. #13
    Moderator VBAX Wizard SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,105
    Location
    Quote Originally Posted by LukeAM View Post
    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.
    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.

    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
    In your situation, each of those Section Head comments could be a place to use a different (subservient) Procedure or Function.
    I always expect the student to do their homework and find all the errrors I leeve in.

    Please take the time to read the Forum FAQ

  14. #14
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    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

  15. #15
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

    Rows(1).Rows(10).Delete
    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).
    ____________________________________________
    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

  16. #16
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

        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
    or even pass just the one not being checked.
    ____________________________________________
    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

  17. #17
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    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.

  18. #18
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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

  19. #19
    VBAX Regular
    Joined
    Aug 2014
    Posts
    21
    Location
    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):
    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
    Do I have to put 'Dim a bunch' at the beginning of this like Sam T suggested? Or something else for it to work??

  20. #20
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    You can't embed the sub within another sub, create them as distinct entities, something like this

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

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •