Compile Error: Procedure too long

[Excel-erate2004]

Hello all,

My problem is fairly straight forward, I need more efficient code as
get: Compile Error: Procedure too long.

I have a button click procedure on my excel spreadsheet which firs
looks at a checkbox on sheet2,

If the checkbox on sheet2 is selected it goes back to sheet1 and look
at a series of 10 checkboxes.

If these checkboxes are not selected it enters in "NA" or "no
applicable" in a range of cells.

The code I have written for this procedure is much too long and
continue to get the compile error mentioned above.

What can I do to shorten my VB code (see small excerpt of code below
to make it more efficient within my Excel spreadsheet.

Thanks for any help I can get. This is definitely a programmin
question.

Code begins:

Private Sub CommandButton1_Click()

IF CheckBoxSpecies1 = True Then

'Bedrock
If Sheets(“Step 1”).CheckBox1 = True Then

Else
Sheets("Step 2").Range("C11:G11") = "NA"
Sheets("Step 2").Range("J11:N11") = "NA"
Sheets("Step 2").Range("C27:G27") = "NA"
Sheets("Step 2").Range("J27:N27") = "NA"
End If

'Boulder
If Sheets(“Step 1”).CheckBox2 = True Then

Else
Sheets("Step 2").Range("C12:G12") = "NA"
Sheets("Step 2").Range("J12:N12") = "NA"
Sheets("Step 2").Range("C28:G28") = "NA"
Sheets("Step 2").Range("J28:N28") = "NA"
End If

'Rubble
If Sheets(“Step 1”).CheckBox3 = True Then

Else
Sheets("Step 2").Range("C13:G13") = "NA"
Sheets("Step 2").Range("J13:N13") = "NA"
Sheets("Step 2").Range("C29:G29") = "NA"
Sheets("Step 2").Range("J29:N29") = "NA"
End If

'Cobble
If Sheets(“Step 1”).CheckBox4 = True Then

Else
Sheets("Step 2").Range("C14:G14") = "NA"
Sheets("Step 2").Range("J14:N14") = "NA"
Sheets("Step 2").Range("C30:G30") = "NA"
Sheets("Step 2").Range("J30:N30") = "NA"
End If

'Gravel
If Sheets(“Step 1”).CheckBox5 = True Then

Else
Sheets("Step 2").Range("C15:G15") = "NA"
Sheets("Step 2").Range("J15:N15") = "NA"
Sheets("Step 2").Range("C31:G31") = "NA"
Sheets("Step 2").Range("J31:N31") = "NA"
End If

'Sand
If Sheets(“Step 1”).CheckBox6 = True Then

Else
Sheets("Step 2").Range("C16:G16") = "NA"
Sheets("Step 2").Range("J16:N16") = "NA"
Sheets("Step 2").Range("C32:G32") = "NA"
Sheets("Step 2").Range("J32:N32") = "NA"
End If

'Silt
If Sheets(“Step 1”).CheckBox7 = True Then

Else
Sheets("Step 2").Range("C17:G17") = "NA"
Sheets("Step 2").Range("J17:N17") = "NA"
Sheets("Step 2").Range("C33:G33") = "NA"
Sheets("Step 2").Range("J33:N33") = "NA"
End If

'Clay
If Sheets(“Step 1”).CheckBox8 = True Then

Else
Sheets("Step 2").Range("C18:G18") = "NA"
Sheets("Step 2").Range("J18:N18") = "NA"
Sheets("Step 2").Range("C34:G34") = "NA"
Sheets("Step 2").Range("J34:N34") = "NA"
End If

'Muck
If Sheets(“Step 1”).CheckBox9 = True Then

Else
Sheets("Step 2").Range("C19:G19") = "NA"
Sheets("Step 2").Range("J19:N19") = "NA"
Sheets("Step 2").Range("C35:G35") = "NA"
Sheets("Step 2").Range("J35:N35") = "NA"
End If

'Pelagic
If Sheets(“Step 1”).Sheets(“Step 1”).CheckBox10 = True Then

Else
Sheets("Step 2").Range("C20:G20") = "NA"
Sheets("Step 2").Range("J20:N20") = "NA"
Sheets("Step 2").Range("C36:G36") = "NA"
Sheets("Step 2").Range("J36:N36") = "NA"
End If

ELSEIF CheckBoxSpecies2 = True Then

'Bedrock
If Sheets(“Step 1”).CheckBox1 = True Then

Else
Sheets("Step 2").Range("C49:G49") = "NA"
Sheets("Step 2").Range("J49:N49") = "NA"
Sheets("Step 2").Range("C65:G65") = "NA"
Sheets("Step 2").Range("J65:N65") = "NA"
End If

'Boulder
If Sheets(“Step 1”).CheckBox2 = True Then

Else
Sheets("Step 2").Range("C50:G50") = "NA"
Sheets("Step 2").Range("J50:N50") = "NA"
Sheets("Step 2").Range("C66:G66") = "NA"
Sheets("Step 2").Range("J66:N66") = "NA"
End If

 

[Edwin Tam]

There are various major errors in your code
1) For the first level of IF-THEN, you missed the End If to close it
If CheckBoxSpecies1 = True The
...
ElseIf CheckBoxSpecies2 = True The
....
End I

2) To refer to a checkbox on a worksheet, the VBA syntax is different from referring to a checkbox on an UserForm
For example, you want to check whether a checkbox called "CheckBox1" on sheet "Step 1" is checked, you use
If Sheets("Step 1").CheckBoxes("CheckBox1").Value = xlOn The
(Use xlOff to test whether it is not checked.

So, use the folowing code

'*******************************************
Option Explici

Private Sub CommandButton1_Click(
Dim tmp_object As Workshee

Set tmp_object = Sheets("Step 1"

With Sheets("Step 2"

If CheckBoxSpecies1 = True The

'Bedroc
If tmp_object.CheckBoxes("CheckBox1").Value = xlOff The
.Range("C11:G11") = "NA
.Range("J11:N11") = "NA
.Range("C27:G27") = "NA
.Range("J27:N27") = "NA
End I

'Boulde
If tmp_object.CheckBoxes("CheckBox2").Value = xlOff The
.Range("C12:G12") = "NA
.Range("J12:N12") = "NA
.Range("C28:G28") = "NA
.Range("J28:N28") = "NA
End I

'Rubbl
If tmp_object.CheckBoxes("CheckBox3").Value = xlOff The
.Range("C13:G13") = "NA
.Range("J13:N13") = "NA
.Range("C29:G29") = "NA
.Range("J29:N29") = "NA
End I

'Cobbl
If tmp_object.CheckBoxes("CheckBox4").Value = xlOff The
.Range("C14:G14") = "NA
.Range("J14:N14") = "NA
.Range("C30:G30") = "NA
.Range("J30:N30") = "NA
End I

'Grave
If tmp_object.CheckBoxes("CheckBox5").Value = xlOff The
.Range("C15:G15") = "NA
.Range("J15:N15") = "NA
.Range("C31:G31") = "NA
.Range("J31:N31") = "NA
End I

'San
If tmp_object.CheckBoxes("CheckBox6").Value = xlOff The
.Range("C16:G16") = "NA
.Range("J16:N16") = "NA
.Range("C32:G32") = "NA
.Range("J32:N32") = "NA
End I

'Sil
If tmp_object.CheckBoxes("CheckBox7").Value = xlOff The
.Range("C17:G17") = "NA
.Range("J17:N17") = "NA
.Range("C33:G33") = "NA
.Range("J33:N33") = "NA
End I

'Cla
If tmp_object.CheckBoxes("CheckBox8").Value = xlOff The
.Range("C18:G18") = "NA
.Range("J18:N18") = "NA
.Range("C34:G34") = "NA
.Range("J34:N34") = "NA
End I

'Muc
If tmp_object.CheckBoxes("CheckBox9").Value = xlOff The
.Range("C19:G19") = "NA
.Range("J19:N19") = "NA
.Range("C35:G35") = "NA
.Range("J35:N35") = "NA
End I

'Pelagi
If tmp_object.CheckBoxes("CheckBox10").Value = xlOff The
.Range("C20:G20") = "NA
.Range("J20:N20") = "NA
.Range("C36:G36") = "NA
.Range("J36:N36") = "NA
End I

ElseIf CheckBoxSpecies2 = True The

'Bedroc
If tmp_object.CheckBoxes("CheckBox1").Value = xlOff The
.Range("C49:G49") = "NA
.Range("J49:N49") = "NA
.Range("C65:G65") = "NA
.Range("J65:N65") = "NA
End I

'Boulde
If tmp_object.CheckBoxes("CheckBox2").Value = xlOff The
.Range("C50:G50") = "NA
.Range("J50:N50") = "NA
.Range("C66:G66") = "NA
.Range("J66:N66") = "NA
End I
End I

End Wit
End Su

'**********************************

Regards

Edwin Ta
http://www.vonixx.co
(e-mail address removed)

 

[Greg Wilson]

I think I would take this approach. If I have it right,
the following code will do the same as your code. I am
assuming that CheckBoxSpecies1 is a module level or public
variable.

Private Sub CommandButton1_Click()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim Rng1 As Range, Rng2 As Range, Rng3 As Range
Dim Rng4 As Range, Rng5 As Range, Rng6 As Range
Dim Rng7 As Range, Rng8 As Range, Rng9 As Range
Dim Rng10 As Range, Rng11 As Range, Rng12 As Range
Dim i As Integer, RngArr As Variant

Set WS1 = Sheets("Step 1")
Set WS2 = Sheets("Step 2")

With WS2
Set Rng1 = .Range("C11:G11, J11:N11, C27:G27, J27:N27")
Set Rng2 = .Range("C12:G12, J12:N12, C28:G28, J28:N28")
Set Rng3 = .Range("C13:G13, J13:N13, C29:G29, J29:N29")
Set Rng4 = .Range("C14:G14, J14:N14, C30:G30, J30:N30")
Set Rng5 = .Range("C15:G15, J15:N15, C31:G31, J31:N31")
Set Rng6 = .Range("C16:G16, J16:N16, C32:G32, J32:N32")
Set Rng7 = .Range("C17:G17, J17:N17, C33:G33, J33:N33")
Set Rng8 = .Range("C18:G18, J18:N18, C34:G34, J34:N34")
Set Rng9 = .Range("C19:G19, J19:N19, C35:G35, J35:N35")
Set Rng10 = .Range("C20:G20, J20:N20, C36:G36, J36:N36")
Set Rng11 = .Range("C49:G49, J49:N49, C65:G65, J65:N65")
Set Rng12 = .Range("C50:G50, J50:N50, C66:G66, J66:N66")
End With

RngArr = Array(Rng1, Rng2, Rng3, Rng4, Rng5, Rng6, _
Rng7, Rng8, Rng9, Rng10, Rng11, Rng12)

If CheckBoxSpecies1 = True Then
For i = 1 To 12
If WS1.OLEObjects("CheckBox" & i).Object _
.Value = False Then RngArr(i - 1).Value = "N/A"
Next
End If

End Sub



Regards,
Greg

 

[Greg Wilson]

Another approach much simpler approach:

Private Sub CommandButton1_Click()
Dim WS1 As Worksheet, WS2 As Worksheet
Dim Rng As Range, i As Integer

Set WS1 = Sheets("Step 1")
Set WS2 = Sheets("Step 2")
Set Rng = WS2.Range("C11:G11, J11:N11, C27:G27, J27:N27")

If CheckBoxSpecies1 = True Then
For i = 1 To 12
If WS1.OLEObjects("CheckBox" & i).Object _
.Value = False Then _
Rng.Offset(i - 1 + IIf(i > 10, 12, 0)).Value = "N/A"
Next
End If

End Sub

Regards,
Greg

 

[Excel-erate2004]

Hi Greg,

I implemented your suggestion and it worked...somewhat, for the 1st o
29 checkboxes.

How my procedure works is it looks at 10 checkboxes on Sheet "Step 1"

Then it looks at 29 checkboxes on Sheet "Step 2".

If the conditions of both checkboxes are false then the procedure wil
enter in an NA in my specified range.

On sheet "Step 2" there are 29 checkboxes.

So if I adapt your solution I will have to take this code:

Dim WS1 As Worksheet, WS2 As Worksheet
Dim Rng As Range, i As Integer

Set WS1 = Sheets("Step 1")
Set WS2 = Sheets("Step 2")
Set Rng = WS2.Range("C11:G11, J11:N11, C27:G27, J27:N27")

If CheckBoxSpecies1 = True Then
For i = 1 To 12
If WS1.OLEObjects("CheckBox" & i).Object _
.Value = False Then _
Rng.Offset(i - 1 + IIf(i > 10, 12, 0)).Value = "N/A"
Next
End If

and rewrite the IF statement 29 times and set up the ranges for each o
the 29 checkboxes as they differ. Hopefully that wont be too long an
give me the same error again.

Thanks for your help

Also to give a better idea of my super long procedure I've attached it

Attachment filename: checkbox.txt
Download attachment: http://www.excelforum.com/attachment.php?postid=52073

 

[Excel-erate2004]

Just wanted to say a huge thanks, your last code post worked perfectly
exactly what I needed!

Greatly appreciate it!

Cheers