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
.