Checkboxes

[Shamsul]

Hi

I'am trying to write code for a number of Checkboxes in a
Userform.

I don't want to repeat the code for each checkbox. I tried
to use the code below, but it didn't work. Can someone
please advise.

Dim ctrl As Control
Dim inct As Long
Dim X As Long
icnt = 0
For Each ctrl In UserForm2.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
icnt = icnt + 1
End If
Next
MsgBox "there are " & icnt & " checboxes"

Range("S3").Select
For X = 1 To icnt
If CheckBoxX = True Then
ActiveCell.FormulaR1C1 = "YES"
ActiveCell.Offset.Select
End If
Next X

 

[Tom Ogilvy]

No sense doing two loops

Dim ctrl As Control
Dim inct As Long
icnt = 0
For Each ctrl In UserForm2.Controls
If TypeOf ctrl Is MSForms.CheckBox Then
ActiveCell.offset(icnt,1).Value = ctrl.caption
if ctrl.Value then
ActiveCell.offset(icnt,0).Value = "Yes"
else
ActiveCell.offst(icnt,0).Value = "No"
end if
End if
icnt = icnt + 1
Next
'MsgBox "there are " & icnt & " checboxes"


If you use Thomas's approach, you will get icnt cells with "yes" in them -
not sure that will tell you much unless you want to count the cells that
contain yes. (However, it does reflect your code). This way, the cells
will correspond to the tabindex property of the checkboxes relative to each
other.