If you need to shift the range and formula items as well within your if-else
tree, you can use R1C1 reference style to make it easier to use the checkbox
number to offset the target ranges. So, if row 3, column 3 is your starting
point trying something like:

BASEROW = 3
BASECOL = 3

if (formName.Controls("CheckBox" & itemNum).Value) then
targetRow = BASEROW + itemNum - 1 '
Range("R" & targetRow & "C3").Formula = "=$R5$C" & (BASECOL + itemNum)
Else

In above, the Left side of the equation varies by row, but is fixed in
column 3 while in the right side, the Row is constant, but the column varies.

"Bill Linker" wrote:

I use the Form's Controls collections and the ability to create a string
index into the collection when I have a problem like this. If you just want
to check all 20 all at once, have an OK or Done button and in the
Button_Click() event have something like (assuming the name of your UserForm
is "formName"):


For itemNum = 1 to 20
if (formName.Controls("CheckBox" & itemNum).Value) then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If

Next itemNum

If you want to respond to each individual click on a checkbox, do it like this

Sub checkbox1_click()
processCBclick(1)
End Sub

Sub checkbox2_click()
processCBclick(2)
End Sub
....
...
Sub checkbox20_click()
processCBclick(20)
End Sub


Sub processCBclick(cbNum as Integer)

if (formName.Controls("CheckBox" & cbNum).Value) then
Range("l(a+1)").Formula = "=$K$33"
Else
Range("l(a+1)").Value = 0
End If

End Sub


To use this you just have to be consistent about naming your controls. You
can expand it to multiple dimension too, though this would probably only
popup in some sort of game:

Sub CheckBoxR1C1_click()
processCBclick(1, 1)
end Sub

Sub CheckBoxR5C3_click()
processCBclick(5,3)
end Sub

Sub processCBclick(rowNum as Integer, colNum as Integer)

if (formName.Controls("CheckBoxR" & rowNum & "C" & colNum).Value) then
'do something
else
'do something else
end if

end Sub