Today must be checkbox question day :-)

[Paul]

Tom Ogilvy just answered a question about how to count
the number of checkboxes. But my similar problem is how to
count the number of checkboxes that have been <checked>. I
tried this line of code as a test, but it bombs :-(


If .CheckBoxes(2).Checked = True Then Range("A1").Value = 1

TIA
Paul

 

[Tom Ogilvy]

A lot would depend on the type of checkbox and where they are located.

If they are on the worksheet and from the forms toolbar, then they are in
the checkboxes collection for that worksheet

Sub AA_TestCheckbox()
For Each chkbx In ActiveSheet.CheckBoxes
If chkbx.Value = xlOn Then
chkbx.TopLeftCell.Offset(0, 2).Value = 1
End If
Next
End Sub


If they are on a worksheet but from the control toolbox toolbar


Sub BB_TestCheckbox()
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSForms.CheckBox Then
If oleObj.Object.Value Then
oleObj.TopLeftCell.Offset(0, 2).Value = 1
End If
End If
Next

End Sub


You should be able to adapt the userform code I provided previously to this
situation if a control toolbox toolbar control is on a userform.