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