User Posts: Priti
0
Types of Sparklines in Excel
0

Excel's Sparklines feature is a helpful tool for identifying data trends. Sparklines are tiny charts that can fit into a single cell and give quick solutions. ...

0
[Fixed!] Excel Date Filter Is Not Grouping by Month (3 Solutions)
0

Filtering data according to particular criteria, like filtering dates by month, is one of the most popular Excel tasks. Excel's date filter occasionally ...

Browsing All Comments By: Priti
  1. Greetings Michal,

    Thanks for your comment! I understand the dissatisfaction with the limitations that you’ve faced. I agree that Microsoft could do more to enhance the functionality of their checkboxes.

    Regarding your first issue, it is true that the COUNTIF function is unable to directly count the number of checked boxes.
    Your second point—that Microsoft does not support Format Control Linking in an array—is also true. This implies that each checkbox needs to be linked separately. This process can take a while, especially if there are a lot of checkboxes.

    However, we can do Format Control Linking using VBA code which we already mentioned in this article. Besides, we are adding another code that works as a function and dynamically does format control linking without any helper column and counts the checked boxes.

    To work with this code, go to the Developer tab, and select Visual Basic. Now, from the Insert tab >> you have to select Module. Write down the following Code in the Module.

    Public Function CheckBoxCount()

    Dim checkBox As Shape
    Dim count As Long

    count = 0

    With ThisWorkbook.ActiveSheet
    For Each checkBox In .Shapes
    If InStr(1, checkBox.Name, “Check Box”) Then
    If .Shapes(checkBox.Name).OLEFormat.Object.Value = 1 Then
    count = count + 1
    End If
    End If
    Next checkBox
    End With

    CheckBoxCount = count

    End Function

    Now, Save the code and go back to Excel File. Insert the following formula in the cell that you want the count of checked boxes.
    =CheckBoxCount()
    And you will have the count of checked boxes.

    Hope this solution helps address your specific requirements in a more efficient manner.

    If you have any further queries, kindly post them on our Exceldemy Forum.
    Have a nice day!

    Regards,
    Priti

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo