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

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





-----Original Message-----
Hello all,

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

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

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

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

The code I have written for this procedure is much too
long and I
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
programming
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
.

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

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
--
Message posted from http://www.ExcelForum.com

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

Greatly appreciate it!

Cheers

--
Message posted from http://www.ExcelForum.com