Formula for counting checked ActiveX boxes

Question

I've entered ActiveX check boxes and need to insert a formula to count the number of boxes checked within the range of cells with check boxes.

Answer 1

Copy the code below into a regular module.

The use a formula like this =CountCheckedBoxes(B5:D9)

Note: If your check boxes did not trigger a calculation in the sheet, the formula might not be updated. In that case you have to write a code in the Change event of each box that forces a recalculation.

Andreas.

Function CountCheckedBoxes(ByVal Where As Range) As Long
  'Counts all checked checkboxes that touch Where
  Dim OO As OLEObject
  'Recall this function every time when a calculation is performed
  Application.Volatile
  'Walk through all objects
  For Each OO In Where.Parent.OLEObjects
    'We are interested in checkboxes only
    If StrComp(OO.progID, "Forms.CheckBox.1", vbTextCompare) = 0 Then
      'Does the cells intersect with this checkbox?
      If Not Intersect(Where, Range(OO.TopLeftCell, OO.BottomRightCell)) Is Nothing Then
        'Count only the checked ones
        If OO.Object.Value Then CountCheckedBoxes = CountCheckedBoxes + 1
      End If
    End If
  Next
End Function

Answer 2

Hi 7Nicholas7

I am Vijay, an Independent Advisor. I am here to work with you on this problem.

Formulas wouldn't work for this. This can be done only through VBA function. Let me know if you need VBA code for this.

Do let me know if you require any further help on this. Will be glad to help you.