PDA

View Full Version : If statement with CommandButton



LukeAM
08-22-2014, 07:05 AM
Is it possible to create an If statement that includes a command button (e.g. the OK button).

Bob Phillips
08-22-2014, 07:57 AM
For what purpose, to rest if it was pressed, to call its Click event, or what?

LukeAM
08-22-2014, 08:08 AM
Yeh for when it is clicked. So something like If OK = Clicked Then do something

Bob Phillips
08-22-2014, 09:46 AM
Then just add your do something to the button's click event, the system monitors when it is clicked.

LukeAM
08-26-2014, 12:47 AM
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

Bob Phillips
08-26-2014, 12:48 AM
What does length have to do with it, if it is a procedure it can be called.

LukeAM
08-26-2014, 12:59 AM
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.

Bob Phillips
08-26-2014, 04:09 AM
I have already shown you how to make that a lot shorter.

LukeAM
08-26-2014, 04:29 AM
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.

Aussiebear
08-26-2014, 11:34 PM
Rather than a command button, simply call another procedure

LukeAM
08-27-2014, 12:21 AM
I've tried calling another procedure but then it doesn't work

Aussiebear
08-27-2014, 03:42 PM
Not having access to your code or workbook doesn't help but I'm very confident that a solution could be reached

SamT
08-27-2014, 04:30 PM
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.

LukeAM
08-28-2014, 12:28 AM
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

Bob Phillips
08-28-2014, 01:40 AM
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).

Bob Phillips
08-28-2014, 01:44 AM
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.

LukeAM
08-28-2014, 01:54 AM
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.

Bob Phillips
08-28-2014, 02:56 AM
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.

LukeAM
08-28-2014, 03:19 AM
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??

Bob Phillips
08-28-2014, 07:22 AM
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.

SamT
08-28-2014, 08:03 AM
LukeAM

This is an example of what I meant by Section Head comments. As you see, they can explain the algorithm of the code.

Private Sub OKClick()

Unload Userform1

''''Get first empty Cell in Column A (rowNr)
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



''''Put Checkbox Values in Column C
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


''''All CheckBoxes = TRUE
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 'All Checkboxes = True
Sheets("Template").Copy
ActiveSheet.Name = "TC1-40"


''''CheckBox 40 = False
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 And CheckBox40 = False Then
Sheets("Template").Copy
ActiveSheet.Name = "TC1-39"
Rows(1).Rows(52).Delete

''''CheckBox 2 = False
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 And CheckBox2 = False Then
Sheets("Template").Copy
ActiveSheet.Name = "TC1,3-40"
Rows(1).Rows(4).Delete

''''CheckBox 3 = False
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 And CheckBox3 = False Then
Sheets("Template").Copy
ActiveSheet.Name = "TC1-2,4-40"
Rows(1).Rows(5).Delete

''''CheckBox 4 = False
ElseIf CheckBox1 = True And CheckBox2 = True And Che

SamT
08-28-2014, 09:16 AM
I (incompletely) refactored your entire code. You will have to go thru all the Checkboxes on your Form and add the appropriate number to their Tag property. Then, (see next para,) complete the code.

As you study this code you will see how I designed it using patterns of values. This makes it very easy to discern where a typographical error occurred. Note that for each Case statement, I merely copied one statement the required number of times, then edited the Values per the pattern of values. Except for Case 20 :LOL

Please note that VBAX enforces its' own ideas of Comment indentation. I always put Section Head Comments at the start of the line.

About "dim a bunch;" I use "Option Explicit" at the top of all my code pages, which requires you to explicitly declare all variables, thus preveenting any un-caught Typos.

Private Sub OKClick()

Const NumCheckBoxes As Long = 40 'Adjust to suit
Const Verifier As Long = NumCheckBoxes * -1 '(True = -1)
Dim Ctrl As Object
Dim Verifying As Long
Dim CheckBoxNumber As Long
Dim RowToDelete As Variant 'Handles Numbers and Strings for CheckBox 20
Dim ShName As String


'''' Verify None or only one Checkbox is False
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is msforms.CheckBox Then
Verifying = Verifying + Ctrl.Value
If Not Ctrl.Value Then CheckBoxNumber = Ctrl.Tag 'Number of False Checkbox
End If
Next Ctrl

If Verifying > Verifier + 1 Then 'Two false CheckBoxes
MsgBox "YOU IDIOT! Only uncheck one box! No Biscuit for you!"
Exit Sub
End If

Unload Userform1 'I hope this is not the form you are working with below

''''Get first empty Cell in Column A (rowNr)
rowNr = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1


''''Put Checkbox Values in Column C
With Worksheets("Data").Columns("C")
.Cells(2) = CheckBox1.Value
.Cells(3) = CheckBox2.Value
.Cells(4) = CheckBox3.Value
.Cells(5) = CheckBox4.Value
.Cells(6) = CheckBox5.Value
.Cells(7) = CheckBox6.Value
.Cells(8) = CheckBox7.Value
.Cells(9) = CheckBox8.Value
.Cells(10) = CheckBox9.Value
.Cells(11) = CheckBox10.Value
End With

''''All CheckBoxes = TRUE
If Verifying = Verifier Then 'All Checkboxes = True
RowToDelete = "none"
ShName = ""
Else
''''Deal With the False CheckBox
Select Case CheckBoxNumber
Case 2
RowToDelete = 4
ShName = ",3"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 4
RowToDelete = 6
ShName = "-3,5"
Case 5
RowToDelete = 7
ShName = "-4,6"
Case 6
RowToDelete = 8
ShName = "-5,7"
Case 7
RowToDelete = 9
ShName = "-6,8"
Case 8
RowToDelete = 10
ShName = "-7,9"
Case 9
RowToDelete = 11
ShName = "-8,10"
Case 10
RowToDelete = 12
ShName = "-9,11"
Case 11
RowToDelete = 13
ShName = "-10,12"
Case 12
RowToDelete = 14
ShName = "-11,13"
Case 13
RowToDelete = 15
ShName = "-4,6"
Case 14
RowToDelete = 16
ShName =
Case 15
RowToDelete = 17
ShName =
Case 16
RowToDelete = 18
ShName =
Case 17
RowToDelete = 19
ShName =
Case 18
RowToDelete = 20
ShName =
Case 19
RowToDelete = 21
ShName =
Case 20
RowToDelete = "22:32"
ShName = "-19,21"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
Case 3
RowToDelete = 5
ShName = "-2,4"
End Select
End If

Sheets("Template").Copy
ActiveSheet.Name = "TC1" & ShName & "-40"
If Not RowToDelete = "none" Then .Rows(RowToDelete).Delete

'More Code

End Sub

SamT
08-28-2014, 09:37 AM
Now that we are dealing with Value patterns, you can see that the RowToDelete is always (Except in Case 20,) The CheckboxNumber + 2 and the shName is always, (except in Case 1,)

"TC1" & "-" & CStr(CheckBoxNumber -1) & "," & CStr(CheckBoxNumber +1 & "-40"

Case 1 is already written and the entire Select Case Statement can be rewritten to

Select Case CheckBoxNumber
Case 20
RowToDelete = "22:32"
ShName = "-19,21"
Case Else
RowToDelete = CheckboxNumber + 2
ShName = "-" & CStr(CheckBoxNumber -1) & "," & CStr(CheckBoxNumber +1)
End Select



Only two Cases vs 39 as I put in the post above.

here is my final (complete) code for you.

Option Explicit

Private Sub OKClick()

Const NumCheckBoxes As Long = 40 'Adjust to suit
Const Verifier As Long = NumCheckBoxes * -1 '(True = -1)
Dim Ctrl As Object
Dim Verifying As Long
Dim CheckBoxNumber As Long
Dim RowToDelete As Variant 'Handles Numbers and Strings for CheckBox 20
Dim ShName As String
Dim i As long


'''' Verify None or only one Checkbox is False
For Each Ctrl In Me.Controls
If TypeOf Ctrl Is msforms.CheckBox Then
Verifying = Verifying + Ctrl.Value
If Not Ctrl.Value Then CheckBoxNumber = Ctrl.Tag 'Number of False Checkbox
End If
Next Ctrl

If Verifying > Verifier + 1 Then 'Two false CheckBoxes
MsgBox "YOU IDIOT! Only uncheck one box! No Biscuit for you!"
Exit Sub
End If

Unload Userform1 'I hope this is not the form you are working with below

''''Get first empty Cell in Column A (rowNr)
rowNr = Worksheets("Data").Cells(Rows.Count, 1).End(xlUp).Row + 1


''''Put Checkbox Values in Column C
With Worksheets("Data").Columns("C")
For i = 1 to 10
.Cells(i + 1) = Me.Controls("CheckBox" & Cstr(i)).Value
Next i
End With

''''All CheckBoxes = TRUE
If Verifying = Verifier Then 'All Checkboxes = True
RowToDelete = "none"
ShName = ""
Else
''''Deal With the False CheckBox
Select Case CheckBoxNumber
Case 20
RowToDelete = "22:32"
ShName = "-19,21"
Case Else
RowToDelete = CheckBoxNumber + 2
ShName = "-" & CStr(CheckBoxNumber - 1) & "," & CStr(CheckBoxNumber + 1)
End Select End If

Sheets("Template").Copy
ActiveSheet.Name = "TC1" & ShName & "-40"
If Not RowToDelete = "none" Then .Rows(RowToDelete).Delete

'More Code

End Sub

LukeAM
08-29-2014, 08:14 AM
Thanks xld for the suggestion but when I use that code the 'OK' doesn't work when clicked upon. I'm trying to rethink my approach, and Sam T has helped me do this. But I'm not sure what other ways there are.

Sam T thanks for your time and effort for helping to show me, it is really appreciated. However, I still don't fully understand (since I'm quite new to VBA). And when using this code, it only kind of works. For example, if I miss out CheckBox39, it will delete row 29. If I miss out CheckBox38 it'll delete row 28 etc. This only happens for tick boxes missed after 20. Also, this is just the start of the code I want to create. Eventually I want to be able to tell VBA what happens if you miss out on 2 or 3 or more boxes. Would it be easier if I sent you my workbook so you had a better understanding of what I am trying to achieve? Thanks again.

SamT
08-29-2014, 08:33 AM
Would it be easier if I sent you my workbook so you had a better understanding of what I am trying to achieve?

Absolutely. I already think that there might be a better way to do what you want. 40 checkboxes!?! Wow!

Use the "Go Advanced" button below the Quick Reply Editor. Below the Advanced Editor, there is a "Manage Attachments" button that will let you upload the workbook here.

LukeAM
09-01-2014, 12:31 AM
OK here it is Sam T. I've had to delete all the text I had with letters (e.g. A, B, C, D etc.) for confidentiality reasons. I've in putted my original code back into this one and you can just copy the code you did if you want to. Thanks very much. And hopefully what I'm trying to achieve is actually possible! Basically I need to tell VBA every possibility of tick boxes that might be 'ticked'. But if you can figure out a code that works, I'll do all the in putting of all the possibilities.

Bob Phillips
09-01-2014, 01:10 AM
Do you realise how many combinations of ticked boxes you can have when you have a set of 40 (clue, it is a lot, a very large lot).

LukeAM
09-01-2014, 01:57 AM
Yes I understand. I have started making a list of all of them. This is why I feel I need to separate my code (whatever code I use or end up using) into different subs and then use an if statement which includes the OK button (otherwise it doesn't work).

Bob Phillips
09-01-2014, 03:01 AM
I think it will take you the rest of your life to make a list of all of them, there are millions. You need a re-think.

LukeAM
09-01-2014, 03:06 AM
I don't think there's that many (although there is a LOT), as it doesn't matter what order the boxes are ticked. For example: boxes 1 and 2, boxes 2 and 1. I will only have to input one of these 2 combinations for VBA to make sense of it. Can you think of any other ways to achieve what I want to achieve then?

Bob Phillips
09-01-2014, 04:16 AM
You reckon?

Just imagine you have 3 checkboxes, the valid combinations are

000
001
010
011
100
101
110
111

With 4 it becomes

0000
0001
0010
0011
0100
0101
0110
0111
1000
1001
1010
1011
1100
1101
1110
1111


With 5 it becomes

00000
00001
00010
00011
00100
00101
00110
00111
01000
01001
01010
01011
01100
01101
01110
01111
10000
10001
10010
10011
10100
10101
10110
10111
11000
11001
11010
11011
11100
11101
11110
11111

Just think what it will be by the time you get to 40, each checkbox doubles the options as you have the previous number with this checkbox unchecked, then the same number again with it checked. You still think it won't be that many?

LukeAM
09-01-2014, 04:54 AM
OK yes there is loads..too many. Is there any other way I can go about this though?

LukeAM
09-01-2014, 06:55 AM
OK I think I have kind of figured out a much easier way. I've made another worksheet called 'Template (2)'. In putted all the text in their and then made an if statement via excel. Now all I want to tell VBA is: if a cell says "N/A" Then delete that row. I'm trying to use this formula but it isn't working.

If Worksheets("Template (2)").Cells("2:2") = "N/A" Then
Rows(1).Rows(2).Delete

SamT
09-01-2014, 07:26 AM
Luke, What you haven't done is tell us WHAT you are trying to do. You have only told us HOW you are trying to do it.

Now that I have seen your workbook, I will attempt to tell us what you are trying to do.

Current existing Objects: Sheets("Template"), UserForm1


"Template" has 52 rows with 40 sections delineated by number in Column A (some merged cells)
UserForm1 has 40 CheckBoxes whose Tag Values correspond to the section numbers on "Template."

Note: Never work on templates. Make a copy and work on the copy.



The goal is to let the User select by CheckBoxes which Sections on the copy of "Template" to delete.

The working sheet should, when done, be named with the Pattern:
"TC & StartSection & If Contiguous(", " & EndContiguousSection &) " - " & StartSection & If Contiguous(", " & EndContiguousSection &) " - " & Etc. (TC3, 12 - 15, 20 - 35, 40)

The Code for copying the template and deleting the Rows is straight forward using a User Defined Collection of CheckBoxes. You must use a collection because the rows must be deleted from the bottom up and a Collection can be looped thru in either direction. (UDC = User Defined Collection)

Placing CheckBox Values on Data is a simple 1 to 40 loop thru the UDC with the Row Number = CheckBox.Tag + 1


Problem in my understanding: Sheets Template and Sheets Data do not match. Your code places the Values of the checkboxes in Data, and deletes sections on Template. What do you intend to do with Sheets Data, Template, (edited,) and the copy of Template(edited)?

At this time I am setting up your UserForm to only use Labels, not CheckBoxes, with color coding to show which Sections to keep, which to delete, and which have not been checked.

Check back tomorrow for the code.

LukeAM
09-01-2014, 07:44 AM
OK thanks so much for your effort SamT I definitely owe you one. Its quite hard to explain so I'll give it a go.

Basically the text in Sheets Data is just a shortened down version of the text in Sheets Template (obviously this is hard to see as I have deleted the text, but you can see the row sizes are much bigger in Sheets Template).

So when you tick the boxes you want it will then pop up a new workbook or sheet with the full version of that text.

So for example if in Sheets Data in B2 it may say "SWFC". You would then tick the checkbox next to "SWFC" and a completely new workbook/sheet would open saying "Sheffield Wednesday Football Club" in the row "B2".

Hope you understand it better now. Or do you want me to give another explanation?

SamT
09-01-2014, 08:53 AM
That's fine. it also meets the specifications of this new UserForm Code I wrote. Replace all the code in the User Form with this code

First: Delete all the Checkboxes, because I can't scroll down the Form in Design mode.
Then Copy the Last two Subs in the code, (LabelXXX_Click and LabelXXX_DblClcik) and Paste them 39 times.
Then change the XXX in the Name to the appropriate Label number and the XXX in the TAg Assignments to the apprpriate Row number String(s)

BTW, the Sub OK_Click is now only seven lines long :D including a check that all controls have been set. The longest procedure is only 13 lines long.

Option Explicit

Dim Sections As Collection

Private Sub Cancel_Click()
Unload Me

End Sub

Private Sub OK_Click()

If Not AllSectionsMarked Then
MsgBox "You must mark every section Green or Red"
Exit Sub
End If

Valuize_Data
DeleteRows

'More Sub Calls here

Unload Me

End Sub

Private Function AllSectionsMarked() As Boolean
Dim i As Long

AllSectionsMarked = True
For i = 1 To 40
If Me.Controls(Sections(i)).BackColor = &HFFFFFF Then
AllSectionsMarked = False
Exit Function
End If
End Function

Private Sub Valuize_Data()
'Put TrueFalse Values in Column C of Sheet "Data"
Dim i As Long
With Worksheets("Data").Columns("C")
For i = 1 To 40
If Me.Controls(Sections(i)).BackColor = &HC0FFC0 Then
.Cells(i + 1) = "True"
Else: .Cells(i + 1) = "False"
End If
Next i
End With

End Sub

Private Sub DeleteRows()
Dim i As Long
With Sheets("Temp")
For i = 40 To 1 Step -1
If Not LCase(Me.Controls(Sections(i)).Tag) = "none" Then _
.Range(Me.Controls(Sections(i)).Tag).EntireRow.Delete
Next i
End With
End Sub


Private Sub UserForm_Initialize()
SetScroolBars
Init_Sections
Init_Tags
Copy_Template

End Sub
Private Sub SetScrollBars()
Me.ScrollBars = fmScrollBarsVertical
Me.ScrollHeight = Me.InsideHeight / 2
End Sub

Private Sub Init_Sections()
Dim i As Long
For i = 1 To 40
Sections.Add Me.Controls("Label" & CStr(i))
Next i
End Sub

Private Sub Init_Tags()
Dim i As Long
For i = 1 To 40
Me.Controls("Label" & CStr(i)).Tag = "none"
Next i
End Sub

Private Sub Copy_Template()
Sheets("Template").Copy After:=Sheets("Data")
ActiveSheet.Name = "Temp"
End Sub

Private Sub Label1_Click()
'If BackColor = Grey Then Single Click turns backcolor Green
'If BC = Green, then turns Red, add Rows do delete to Tag
'If BC = Red, Then Turn Green, Add "none" to Tag

With Me.Label1
If .BackColor = &HFFFFFF Then 'Grey
.Tag = "None" 'Tag Used to Delete Rows
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0FFC0 Then 'Green
.Tag = "A1:A2" 'Tag Value = Address of First Cell(s) in Row(s)
.BackColor = &HC0C0FF
ElseIf .BackColor = &HC0C0FF Then 'Red
.Tag = "None" 'Tag Used to Delete Rows
.BackColor = &HC0FFC0
End If
End With
End Sub

Private Sub Label1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'If BackColor = Grey, Then turn BC = Red, Add Rows to Tag
'If BC = Green, Then turn BC = Red, Add Rows to Tag
'If BC = Red, Then turn BC to Green, Add "none" to Tag
Cancel = True

With Me.Label1
If .BackColor = &HFFFFFF Then 'Grey
.Tag = "A1:A2"
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0FFC0 Then 'Green
.Tag = "A1:A2"
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0C0FF Then 'Red
.Tag = "None"
.BackColor = &HC0FFC0
End If
End With
End Sub

Private Sub LabelXXX_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Copy and paste these two LabelXXX subs 39 times and edit XXX to reflect
'the Label Name numbers and the Tag Values
With Me.LabelXXX
If .BackColor = &HFFFFFF Then 'Grey
.Tag = "XXX"
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0FFC0 Then 'Green
.Tag = "XXX"
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0C0FF Then 'Red
.Tag = "None"
.BackColor = &HC0FFC0
End If
End With
End Sub

Private Sub LabelXXX_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'Copy and Paste with above LabelXXX sub
Cancel = True

With Me.LabelXXX
If .BackColor = &HFFFFFF Then 'Grey
.Tag = "XXX"
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0FFC0 Then 'Green
.Tag = "XXX"
.BackColor = &HC0FFC0
ElseIf .BackColor = &HC0C0FF Then 'Red
.Tag = "None"
.BackColor = &HC0FFC0
End If
End With
End Sub

SamT
09-01-2014, 09:06 AM
BTW, for future reference, If a label is not going to be clicked or otherwise used, leave it with the default name (Label1). On the other hand, like in this Form, if the Label will be used for some purpose, change its name to reflect the use or data structure or other mnemonic. In this case I would have used "lblBBClub1" thru "lblBBClub40" or even just "Club1" thru "Club40", being aware that I don't have a clue what the Sections really represent.

LukeAM
09-02-2014, 01:17 AM
Thanks a lot again Sam, you really are a 'VBA Master'. I just have some small questions though: the last 2 subs in this code are both LabelXXXDbl_Click, is it these that need to be copied down or did you mean to write one as LabelXXX_Click.


Also, where you have put ''More sub calls here'. What subs do you want me to call? Call 'Label1_Click()' and all the others?.


Finally I'm not too sure what you mean by inputting the appropriate Row number String(s) in the tag assignment. For example in the 'Properties' part for 'Label1' where it says 'Tag' I input the cell rows for that label? In this case row 2 and 3. And for 'Label2' it would be row 4.


Also I am getting an error and it is highlighting in yellow the 'Private Function AllSectionsMarked() As Boolean' line.

SamT
09-02-2014, 06:48 AM
I wrote LabelXXX's so that you could copy them down for the rest of the 40 cases. In the sub names, you have to replace XXX with the label number.

In the Tag XXX'x you have to replace the XXX's with a Range Address that is in the Row(s) you want deleted Example Lable20.Tag = "A22:A32". Note that you could use "Z22:Z32" or "A22:Z32". All that is important, is that it is a valid Range Address.

One of the first things initialized in the Form initialize sub is to run InitSections. Since all the important Labels can be accessed by looping thru the Sections collection, all work that is performed on all those labels can be done with a short loop of code.

When the User clicks on a grey label,it will turn green, if he clicks again, it will turn red, If he doubleclciks on a grey label, it will immediately turn red. If he clicks or d-clicks on a red or green label, it will turn the other color. The Rows in the tags of all red labels will be deleted.

"Put more Sub [calls] here" is because you have said that you still need to add more code to the project. Ignore it and delete it.

The code in the module is roughly organized with the command Button subs first followed by the subs they call, then the Form_Initialize and the subs it calls then by the 80 Label click:d-click subs, which are of the type, "if you've seen one, you've seen them all."


highlighting in yellow the 'Private Function AllSectionsMarked() As Boolean' line. The line is yellow because it is the next line to be executed. (It hasn't run yet.) the problem is inside the sub. What is the error?

There are three main checks built into this code
1. The user can tell at a glance which labels have been processed and which are set for deletion.
2. The OK_Click sub won't run if any labels have not been clicked.
3. Even if it did run, no row of any unclicked label will be deleted.

Bob Phillips
09-02-2014, 07:12 AM
There is a Next i missing from the Function AllSectionsMarked.

SamT
09-02-2014, 10:51 AM
thanks

SamT
09-02-2014, 04:58 PM
OK, I refactored the code for the Label Click and DblClicks

Just replace everything, including from the first label1_cllik sub to the bottom with the code below. Only the top most sub below, (SetMyTag,) needs to be edited to show what rows to delete.. I completed all the Clcik and DblClcik subs because it was really trivial with my editor (UltraEdit.)

What now happens is that when you click a label, the label control calls the back coloring sub(s) and passes its own name to the BC Sub. It then calls the SetMyTag sub and again passes its name.

I put the subs in this order, becvause the SetMyTag sub is the only one that needs changing, and all the clciky subs are identical. You still have to add the Label number to some of the "Cases" (Case "LabelNNN") and you will have to Replace XXX with a Cell address (in the rows to be deleted according to each label for the) .Value = "XXX" parts.


SetMyTag(CtrlName As String)
With Me.Controls(CtrlName).Tag
If .Backcolor = &HC0C0FF, Then 'BC is red, to have a Row(s) deleted
Select Case CtrlName
Case "Label1"
.Value = "A1:A2" 'Example. If Label1 is red. Delete Rows 1 and 2
Case "Label2"
.Value = "A3" 'Example. If Label2 is red, delete Row 3
Case "Label3"
.Value = "XXX"
Case "Label4"
.Value = "XXX"
Case "Label5"
.Value = "XXX"
Case "Label6"
.Value = "XXX"
Case "Label7"
.Value = "XXX"
Case "Label8"
.Value = "XXX"
Case "Label9"
.Value = "XXX"
Case "Label10"
.Value = "XXX"
Case "Label11"
.Value = "XXX"
Case "Label12"
.Value = "XXX"
Case "Label13"
.Value = "XXX"
Case "Label14"
.Value = "XXX"
Case "Label15"
.Value = "XXX"
Case "Label16"
.Value = "XXX"
Case "Label17"
.Value = "XXX"
Case "Label18"
.Value = "XXX"
Case "Label19"
.Value = "XXX"
Case "Label20"
.Value = "XXX"
Case "Label21"
.Value = "XXX"
Case "Label22"
.Value = "XXX"
Case "Label23"
.Value = "XXX"
Case "Label24"
.Value = "XXX"
Case "Label25"
.Value = "XXX"
Case "Label26"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
Case "LableNNN"
.Value = "XXX"
End Select
Else: .Value = "none"
End If
End With
End Sub


Private Sub BackColorMe1(CtrlName as String)
'Handles SingleClicking controls
'If BackColor = Grey Then Single Click turns backcolor Green
'If BC = Green, then turns Red
'If BC = Red, Then Turn Green

With Me.Controls(CtrlName)
If .BackColor = &HFFFFFF Then 'is Grey
.BackColor = &HC0FFC0 'turn Green
ElseIf .BackColor = &HC0FFC0 Then 'is Green
.BackColor = &HC0C0FF 'Turn red
ElseIf .BackColor = &HC0C0FF Then 'is Red
.BackColor = &HC0FFC0 'turn green
End If
End With
End Sub

Private Sub BackColorMe2(CtrlName as String)
'Handles doubleclicking Controls
'If BackColor = Grey, Then turn BC = Red
'If BC = Green, Then turn BC = Red
'If BC = Red, Then turn BC to Green

With Me.Controls(CtrlName)
If .BackColor = &HFFFFFF Then 'is Grey
.BackColor = &HC0FFC0 'turn Red
ElseIf .BackColor = &HC0FFC0 Then 'is Green
.BackColor = &HC0FFC0 'Turn red
ElseIf .BackColor = &HC0C0FF Then 'is Red
.BackColor = &HC0FFC0 'Turn green
End If
End With
End Sub


Private Sub Label1_Click()
BackColorMe1("Label1")
SetMyTag("Label1")
End Sub

Private Sub Label1_DblClick()
BackColorMe1("Label1")
SetMyTag("Label1")
End Sub

Private Sub Label2_Click()
BackColorMe1("Label2")
SetMyTag("Label2")
End Sub

Private Sub Label2_DblClick()
BackColorMe1("Label2")
SetMyTag("Label2")
End Sub

Private Sub Label3_Click()
BackColorMe1("Label3")
SetMyTag("Label3")
End Sub

Private Sub Label3_DblClick()
BackColorMe1("Label3")
SetMyTag("Label3")
End Sub

Private Sub Label4_Click()
BackColorMe1("Label4")
SetMyTag("Label4")
End Sub

Private Sub Label4_DblClick()
BackColorMe1("Label4")
SetMyTag("Label4")
End Sub

Private Sub Label5_Click()
BackColorMe1("Label5")
SetMyTag("Label5")
End Sub

Private Sub Label5_DblClick()
BackColorMe1("Label5")
SetMyTag("Label5")
End Sub

Private Sub Label6_Click()
BackColorMe1("Label6")
SetMyTag("Label6")
End Sub

Private Sub Label6_DblClick()
BackColorMe1("Label6")
SetMyTag("Label6")
End Sub

Private Sub Label7_Click()
BackColorMe1("Label7")
SetMyTag("Label7")
End Sub

Private Sub Label7_DblClick()
BackColorMe1("Label7")
SetMyTag("Label7")
End Sub

Private Sub Label8_Click()
BackColorMe1("Label8")
SetMyTag("Label8")
End Sub

Private Sub Label8_DblClick()
BackColorMe1("Label8")
SetMyTag("Label8")
End Sub

Private Sub Label9_Click()
BackColorMe1("Label9")
SetMyTag("Label9")
End Sub

Private Sub Label9_DblClick()
BackColorMe1("Label9")
SetMyTag("Label9")
End Sub

Private Sub Label10_Click()
BackColorMe1("Label10")
SetMyTag("Label10")
End Sub

Private Sub Label10_DblClick()
BackColorMe1("Label10")
SetMyTag("Label10")
End Sub

Private Sub Label11_Click()
BackColorMe1("Label11")
SetMyTag("Label11")
End Sub

Private Sub Label11_DblClick()
BackColorMe1("Label11")
SetMyTag("Label11")
End Sub

Private Sub Label12_Click()
BackColorMe1("Label12")
SetMyTag("Label12")
End Sub

Private Sub Label12_DblClick()
BackColorMe1("Label12")
SetMyTag("Label12")
End Sub

Private Sub Label13_Click()
BackColorMe1("Label13")
SetMyTag("Label13")
End Sub

Private Sub Label13_DblClick()
BackColorMe1("Label13")
SetMyTag("Label13")
End Sub

Private Sub Label14_Click()
BackColorMe1("Label14")
SetMyTag("Label14")
End Sub

Private Sub Label14_DblClick()
BackColorMe1("Label14")
SetMyTag("Label14")
End Sub

Private Sub Label15_Click()
BackColorMe1("Label15")
SetMyTag("Label15")
End Sub

Private Sub Label15_DblClick()
BackColorMe1("Label15")
SetMyTag("Label15")
End Sub

Private Sub Label16_Click()
BackColorMe1("Label16")
SetMyTag("Label16")
End Sub

Private Sub Label16_DblClick()
BackColorMe1("Label16")
SetMyTag("Label16")
End Sub

Private Sub Label17_Click()
BackColorMe1("Label17")
SetMyTag("Label17")
End Sub

Private Sub Label17_DblClick()
BackColorMe1("Label17")
SetMyTag("Label17")
End Sub

Private Sub Label18_Click()
BackColorMe1("Label18")
SetMyTag("Label18")
End Sub

Private Sub Label18_DblClick()
BackColorMe1("Label18")
SetMyTag("Label18")
End Sub

Private Sub Label19_Click()
BackColorMe1("Label19")
SetMyTag("Label19")
End Sub

Private Sub Label19_DblClick()
BackColorMe1("Label19")
SetMyTag("Label19")
End Sub

Private Sub Label20_Click()
BackColorMe1("Label20")
SetMyTag("Label20")
End Sub

Private Sub Label20_DblClick()
BackColorMe1("Label20")
SetMyTag("Label20")
End Sub

Private Sub Label21_Click()
BackColorMe1("Label21")
SetMyTag("Label21")
End Sub

Private Sub Label21_DblClick()
BackColorMe1("Label21")
SetMyTag("Label21")
End Sub

Private Sub Label22_Click()
BackColorMe1("Label22")
SetMyTag("Label22")
End Sub

Private Sub Label22_DblClick()
BackColorMe1("Label22")
SetMyTag("Label22")
End Sub

Private Sub Label23_Click()
BackColorMe1("Label23")
SetMyTag("Label23")
End Sub

Private Sub Label23_DblClick()
BackColorMe1("Label23")
SetMyTag("Label23")
End Sub

Private Sub Label24_Click()
BackColorMe1("Label24")
SetMyTag("Label24")
End Sub

Private Sub Label24_DblClick()
BackColorMe1("Label24")
SetMyTag("Label24")
End Sub

Private Sub Label25_Click()
BackColorMe1("Label25")
SetMyTag("Label25")
End Sub

Private Sub Label25_DblClick()
BackColorMe1("Label25")
SetMyTag("Label25")
End Sub

Private Sub Label26_Click()
BackColorMe1("Label26")
SetMyTag("Label26")
End Sub

Private Sub Label26_DblClick()
BackColorMe1("Label26")
SetMyTag("Label26")
End Sub

Private Sub Label27_Click()
BackColorMe1("Label27")
SetMyTag("Label27")
End Sub

Private Sub Label27_DblClick()
BackColorMe1("Label27")
SetMyTag("Label27")
End Sub

Private Sub Label28_Click()
BackColorMe1("Label28")
SetMyTag("Label28")
End Sub

Private Sub Label28_DblClick()
BackColorMe1("Label28")
SetMyTag("Label28")
End Sub

Private Sub Label29_Click()
BackColorMe1("Label29")
SetMyTag("Label29")
End Sub

Private Sub Label29_DblClick()
BackColorMe1("Label29")
SetMyTag("Label29")
End Sub

Private Sub Label30_Click()
BackColorMe1("Label30")
SetMyTag("Label30")
End Sub

Private Sub Label30_DblClick()
BackColorMe1("Label30")
SetMyTag("Label30")
End Sub

Private Sub Label31_Click()
BackColorMe1("Label31")
SetMyTag("Label31")
End Sub

Private Sub Label31_DblClick()
BackColorMe1("Label31")
SetMyTag("Label31")
End Sub

Private Sub Label32_Click()
BackColorMe1("Label32")
SetMyTag("Label32")
End Sub

Private Sub Label32_DblClick()
BackColorMe1("Label32")
SetMyTag("Label32")
End Sub

Private Sub Label33_Click()
BackColorMe1("Label33")
SetMyTag("Label33")
End Sub

Private Sub Label33_DblClick()
BackColorMe1("Label33")
SetMyTag("Label33")
End Sub

Private Sub Label34_Click()
BackColorMe1("Label34")
SetMyTag("Label34")
End Sub

Private Sub Label34_DblClick()
BackColorMe1("Label34")
SetMyTag("Label34")
End Sub

Private Sub Label35_Click()
BackColorMe1("Label35")
SetMyTag("Label35")
End Sub

Private Sub Label35_DblClick()
BackColorMe1("Label35")
SetMyTag("Label35")
End Sub

Private Sub Label36_Click()
BackColorMe1("Label36")
SetMyTag("Label36")
End Sub

Private Sub Label36_DblClick()
BackColorMe1("Label36")
SetMyTag("Label36")
End Sub

Private Sub Label37_Click()
BackColorMe1("Label37")
SetMyTag("Label37")
End Sub

Private Sub Label37_DblClick()
BackColorMe1("Label37")
SetMyTag("Label37")
End Sub

Private Sub Label38_Click()
BackColorMe1("Label38")
SetMyTag("Label38")
End Sub

Private Sub Label38_DblClick()
BackColorMe1("Label38")
SetMyTag("Label38")
End Sub

Private Sub Label39_Click()
BackColorMe1("Label39")
SetMyTag("Label39")
End Sub

Private Sub Label39_DblClick()
BackColorMe1("Label39")
SetMyTag("Label39")
End Sub

Private Sub Label40_Click()
BackColorMe1("Label40")
SetMyTag("Label40")
End Sub

Private Sub Label40_DblClick()
BackColorMe1("Label40")
SetMyTag("Label40")
End Sub



Private Sub BackColorMe1(CtrlName as String)
'Handles SingleClicked controls
'If BackColor = Grey Then Single Click turns backcolor Green
'If BC = Green, then turns Red
'If BC = Red, Then Turn Green

With Me.Controls(CtrlName)
If .BackColor = &HFFFFFF Then 'is Grey
.BackColor = &HC0FFC0 'turn Green
ElseIf .BackColor = &HC0FFC0 Then 'is Green
.BackColor = &HC0C0FF 'Turn red
ElseIf .BackColor = &HC0C0FF Then 'is Red
.BackColor = &HC0FFC0 'turn green
End If
End With
End Sub

Private Sub BackColorMe2(CtrlName as String)
'Handles doubleclicked Controls
'If BackColor = Grey, Then turn BC = Red
'If BC = Green, Then turn BC = Red
'If BC = Red, Then turn BC to Green

With Me.Controls(CtrlName)
If .BackColor = &HFFFFFF Then 'is Grey
.BackColor = &HC0FFC0 'turn Red
ElseIf .BackColor = &HC0FFC0 Then 'is Green
.BackColor = &HC0FFC0 'Turn red
ElseIf .BackColor = &HC0C0FF Then 'is Red
.BackColor = &HC0FFC0 'Turn green
End If
End With
End Sub


Private Sub Label1_Click()
BackColorMe1("Label1")
SetMyTag("Label1")
End Sub

Private Sub Label1_DblClick()
BackColorMe1("Label1")
SetMyTag("Label1")
End Sub

Private Sub Label2_Click()
BackColorMe1("Label2")
SetMyTag("Label2")
End Sub

Private Sub Label2_DblClick()
BackColorMe1("Label2")
SetMyTag("Label2")
End Sub

Private Sub Label3_Click()
BackColorMe1("Label3")
SetMyTag("Label3")
End Sub

Private Sub Label3_DblClick()
BackColorMe1("Label3")
SetMyTag("Label3")
End Sub

Private Sub Label4_Click()
BackColorMe1("Label4")
SetMyTag("Label4")
End Sub

Private Sub Label4_DblClick()
BackColorMe1("Label4")
SetMyTag("Label4")
End Sub

Private Sub Label5_Click()
BackColorMe1("Label5")
SetMyTag("Label5")
End Sub

Private Sub Label5_DblClick()
BackColorMe1("Label5")
SetMyTag("Label5")
End Sub

Private Sub Label6_Click()
BackColorMe1("Label6")
SetMyTag("Label6")
End Sub

Private Sub Label6_DblClick()
BackColorMe1("Label6")
SetMyTag("Label6")
End Sub

Private Sub Label7_Click()
BackColorMe1("Label7")
SetMyTag("Label7")
End Sub

Private Sub Label7_DblClick()
BackColorMe1("Label7")
SetMyTag("Label7")
End Sub

Private Sub Label8_Click()
BackColorMe1("Label8")
SetMyTag("Label8")
End Sub

Private Sub Label8_DblClick()
BackColorMe1("Label8")
SetMyTag("Label8")
End Sub

Private Sub Label9_Click()
BackColorMe1("Label9")
SetMyTag("Label9")
End Sub

Private Sub Label9_DblClick()
BackColorMe1("Label9")
SetMyTag("Label9")
End Sub

Private Sub Label10_Click()
BackColorMe1("Label10")
SetMyTag("Label10")
End Sub

Private Sub Label10_DblClick()
BackColorMe1("Label10")
SetMyTag("Label10")
End Sub

Private Sub Label11_Click()
BackColorMe1("Label11")
SetMyTag("Label11")
End Sub

Private Sub Label11_DblClick()
BackColorMe1("Label11")
SetMyTag("Label11")
End Sub

Private Sub Label12_Click()
BackColorMe1("Label12")
SetMyTag("Label12")
End Sub

Private Sub Label12_DblClick()
BackColorMe1("Label12")
SetMyTag("Label12")
End Sub

Private Sub Label13_Click()
BackColorMe1("Label13")
SetMyTag("Label13")
End Sub

Private Sub Label13_DblClick()
BackColorMe1("Label13")
SetMyTag("Label13")
End Sub

Private Sub Label14_Click()
BackColorMe1("Label14")
SetMyTag("Label14")
End Sub

Private Sub Label14_DblClick()
BackColorMe1("Label14")
SetMyTag("Label14")
End Sub

Private Sub Label15_Click()
BackColorMe1("Label15")
SetMyTag("Label15")
End Sub

Private Sub Label15_DblClick()
BackColorMe1("Label15")
SetMyTag("Label15")
End Sub

Private Sub Label16_Click()
BackColorMe1("Label16")
SetMyTag("Label16")
End Sub

Private Sub Label16_DblClick()
BackColorMe1("Label16")
SetMyTag("Label16")
End Sub

Private Sub Label17_Click()
BackColorMe1("Label17")
SetMyTag("Label17")
End Sub

Private Sub Label17_DblClick()
BackColorMe1("Label17")
SetMyTag("Label17")
End Sub

Private Sub Label18_Click()
BackColorMe1("Label18")
SetMyTag("Label18")
End Sub

Private Sub Label18_DblClick()
BackColorMe1("Label18")
SetMyTag("Label18")
End Sub

Private Sub Label19_Click()
BackColorMe1("Label19")
SetMyTag("Label19")
End Sub

Private Sub Label19_DblClick()
BackColorMe1("Label19")
SetMyTag("Label19")
End Sub

Private Sub Label20_Click()
BackColorMe1("Label20")
SetMyTag("Label20")
End Sub

Private Sub Label20_DblClick()
BackColorMe1("Label20")
SetMyTag("Label20")
End Sub

Private Sub Label21_Click()
BackColorMe1("Label21")
SetMyTag("Label21")
End Sub

Private Sub Label21_DblClick()
BackColorMe1("Label21")
SetMyTag("Label21")
End Sub

Private Sub Label22_Click()
BackColorMe1("Label22")
SetMyTag("Label22")
End Sub

Private Sub Label22_DblClick()
BackColorMe1("Label22")
SetMyTag("Label22")
End Sub

Private Sub Label23_Click()
BackColorMe1("Label23")
SetMyTag("Label23")
End Sub

Private Sub Label23_DblClick()
BackColorMe1("Label23")
SetMyTag("Label23")
End Sub

Private Sub Label24_Click()
BackColorMe1("Label24")
SetMyTag("Label24")
End Sub

Private Sub Label24_DblClick()
BackColorMe1("Label24")
SetMyTag("Label24")
End Sub

Private Sub Label25_Click()
BackColorMe1("Label25")
SetMyTag("Label25")
End Sub

Private Sub Label25_DblClick()
BackColorMe1("Label25")
SetMyTag("Label25")
End Sub

Private Sub Label26_Click()
BackColorMe1("Label26")
SetMyTag("Label26")
End Sub

Private Sub Label26_DblClick()
BackColorMe1("Label26")
SetMyTag("Label26")
End Sub

Private Sub Label27_Click()
BackColorMe1("Label27")
SetMyTag("Label27")
End Sub

Private Sub Label27_DblClick()
BackColorMe1("Label27")
SetMyTag("Label27")
End Sub

Private Sub Label28_Click()
BackColorMe1("Label28")
SetMyTag("Label28")
End Sub

Private Sub Label28_DblClick()
BackColorMe1("Label28")
SetMyTag("Label28")
End Sub

Private Sub Label29_Click()
BackColorMe1("Label29")
SetMyTag("Label29")
End Sub

Private Sub Label29_DblClick()
BackColorMe1("Label29")
SetMyTag("Label29")
End Sub

Private Sub Label30_Click()
BackColorMe1("Label30")
SetMyTag("Label30")
End Sub

Private Sub Label30_DblClick()
BackColorMe1("Label30")
SetMyTag("Label30")
End Sub

Private Sub Label31_Click()
BackColorMe1("Label31")
SetMyTag("Label31")
End Sub

Private Sub Label31_DblClick()
BackColorMe1("Label31")
SetMyTag("Label31")
End Sub

Private Sub Label32_Click()
BackColorMe1("Label32")
SetMyTag("Label32")
End Sub

Private Sub Label32_DblClick()
BackColorMe1("Label32")
SetMyTag("Label32")
End Sub

Private Sub Label33_Click()
BackColorMe1("Label33")
SetMyTag("Label33")
End Sub

Private Sub Label33_DblClick()
BackColorMe1("Label33")
SetMyTag("Label33")
End Sub

Private Sub Label34_Click()
BackColorMe1("Label34")
SetMyTag("Label34")
End Sub

Private Sub Label34_DblClick()
BackColorMe1("Label34")
SetMyTag("Label34")
End Sub

Private Sub Label35_Click()
BackColorMe1("Label35")
SetMyTag("Label35")
End Sub

Private Sub Label35_DblClick()
BackColorMe1("Label35")
SetMyTag("Label35")
End Sub

Private Sub Label36_Click()
BackColorMe1("Label36")
SetMyTag("Label36")
End Sub

Private Sub Label36_DblClick()
BackColorMe1("Label36")
SetMyTag("Label36")
End Sub

Private Sub Label37_Click()
BackColorMe1("Label37")
SetMyTag("Label37")
End Sub

Private Sub Label37_DblClick()
BackColorMe1("Label37")
SetMyTag("Label37")
End Sub

Private Sub Label38_Click()
BackColorMe1("Label38")
SetMyTag("Label38")
End Sub

Private Sub Label38_DblClick()
BackColorMe1("Label38")
SetMyTag("Label38")
End Sub

Private Sub Label39_Click()
BackColorMe1("Label39")
SetMyTag("Label39")
End Sub

Private Sub Label39_DblClick()
BackColorMe1("Label39")
SetMyTag("Label39")
End Sub

Private Sub Label40_Click()
BackColorMe1("Label40")
SetMyTag("Label40")
End Sub

Private Sub Label40_DblClick()
BackColorMe1("Label40")
SetMyTag("Label40")
End Sub

LukeAM
09-03-2014, 01:28 AM
Thanks a lot SamT again. I have copied and edited that code from the point where you said, this point is after the 'Copy_Template()' sub. Now though the 'SetMyTag(CtrlName As String)' is in red, I have just put 'Private Sub' in front of this to solve this problem.

Also the code you have just posted from 'Private Sub BackColorMe1 (CtrlName As String)' I believe has been copied down twice by accident? Therefore, the error 'Ambiguous name detected' is occuring. I have deleted what I think is the accidental second copy and now it is coming up with the error 'Procedure declaration does not match description of even or procedure having the same name'.

SamT
09-03-2014, 02:27 PM
There should be two versions of BackcolorMe: Version 1 ("BackColorMe1") is used by the Label_Click subs and Version2 ("BackColorMe2") is used by the Label_DblClick subs. (v1 = Click. v2 = Clickclick:D)

I see that I called Version1 in the Label_DblClick subs. They should call Version2.

My very bad. I am sorry. I leave it to you to correct that.

The rationale behind two BackColorMe versions: In all but the default state of Grey back colors, the two work identically. IE, they merely swap the colors between Red and Green. In the Case where the Label has not yet been clicked at all, Version 1 sets the BC to green and Version2 sets it to Red.

The reason is that your users will quickly get used to clicking twice to turn the BC red. Eventually, they will click twice so fast that it is a DblClick. If there was not a Label_DblClick sub, the user would be frustrated by having to wait a second and try again slower. The Green/Red swapping part of Version2 is just in case Some Users have twitchy trigger fingers like I do :D

LukeAM
09-04-2014, 08:30 AM
OK thanks Sam T. I'll do it now and hope it works :)