Hi all.

I am currently working on a user form and want a cell to return a value based on the status of the checkbox(true or false). However, I have 30 Checkboxes to loop through (10 sets of Yes, No and NA) so I was wondering if I could do this dynamically. Here is the code I have so far, but it keeps getting stuck on the Controls("CheckBoxYes" & i).Caption:

Private Sub CommandButton1_Click()
Dim emptyRow As Long
Dim CriteriaAnswerYes As Control
Dim CriteriaAnswerNo As Control
Dim CriteriaAnswerNA As Control

Dim i As Long


For i = 1 To 10

Set CriteriaAnswerYes = Controls("CheckBoxYes" & i)
Set CriteriaAnswerNo = Controls("CheckBoxNo" & i)
Set CriteriaAnswerNA = Controls("CheckBoxNA" & i)

If CriteriaAnswerYes.Value = True And CriteriaAnswerNo.Value = True Then
MsgBox "Please only select one value from the checkboxes!", vbCritical
Exit Sub
ElseIf CriteriaAnswerYes.Value = True And CriteriaAnswerNA.Value = True Then
MsgBox "Please only select one value from the checkboxes!", vbCritical
Exit Sub
ElseIf CriteriaAnswerNo.Value = True And CriteriaAnswerNA.Value = True Then
MsgBox "Please only select one value from the checkboxes!", vbCritical
Exit Sub

Else
If CriteriaAnswerYes.Value = True Then
Cells(emptyRow, i + 6).Value = CriteriaAnswerYes.Caption
ElseIf CriteriaAnswerNo.Value = True Then
Cells(emptyRow, i + 6).Value = CriteriaAnswerNo.Caption
ElseIf CriteriaAnswerNA.Value = True Then
Cells(emptyRow, i + 6).Value = CriteriaAnswerNA.Caption
End If
End If
Next i

End Sub


Thank you in advance.

Cheers.