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, out of my league.

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
.
.
.
[Continues thru to CheckBoxSpecies29, so 1- 29]

END IF

End Sub

Any chance you can post the xls? Chances are you need to do more work in the spreadsheet and less in VBA. You'll have to zip it before you post.

Can't post the xls, for obvious reasons, but I can post the entire code procedure. Not sure if this will help. But worth a try.

Thanks

Hi

how about using your code like this

ive done the first 3 species checkboxes and the first 2 checkboxes
for the further species checkboxes myVal1 and myVal2 are just the rows on which the first lines of "NA" are Supposed to go
in The Subsequent Checkboxs Checkbox3 etc. keep adding to the value after myval and myval2 in such a way that checkboxN add N-1

Hope this Helps

David

Hello David,

Thanks for your help, the code did work out. I also have an alternative method for anybody visiting this post sometime in the future.

This code works as well:

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 Rng1 = WS2.Range("C11:G11, J11:N11, C27:G27, J27:N27")
...
Set Rng29 = WS2.Range("C1075:G1075, J1075:N1075, C1091:G1091, J1091:N1091")


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

Thanks again!

Cheers