Unable to locate the checkboxes in excel - win32com.client package

Question

I am trying to pull the rows from an excel for which the checkbox is ticked, using python.

Sample code I have used is:

excel = win32.gencache.EnsureDispatch('Excel.Application')              
wb = excel.Workbooks.Open(file_path)              
ws = wb.Worksheets("Sheet1")              
            
# Get the values of all the checkboxes in the worksheet              
cb_dict = {}              
for cb in ws.CheckBoxes():              
    cb_dict[cb.Name] = cb.Value            
    # Check if checkbox value is 1.0 and get the cell location            
    if cb.Value == 1.0:            
        cell = ws.CheckBoxes(cb.Name).TopLeftCell            
        # Get the values of columns A and B based on the cell address    
        client_name = ws.Range("A" + str(cell.Row)).Value    
        ledger_no = ws.Range("B" + str(cell.Row)).Value 

I noticed that, it started to pull too many checkboxes, and when I went to inspect the excel, I found that there are 300+ checkboxes that are present in the selection pane while only 14 are visible as shown below.

There are 100's of excel file that I need to process and I fear most of them have this scenario.

1. How can I find where the checkboxes are located (I tried, hide/unhide etc)
2. Is there any workaround I can run in my script to bypass the unwanted checkboxes?

Any links or workaround would be helpful.

Answer 1

As already written as comment: I assume those checkboxes where added via code, and the code was executed multiple times, so there are multiple checkboxes on top of each other.

You can check if a checkbox is painted above another by comparing the property zOrder. A lower value means the checkbox is below another checkbox.

If two (or more) checkboxes are exactly on top of each other, the user will see only the topmost checkbox. Consequently, only the topmost checkbox can be selected (or deselected), and you will be interested only in those checkboxes.

The following (VBA) code will loop over all checkboxes and check if it is a "topmost" checkbox. You can decide by your own what to do with the "extra" checkboxes: You can delete or just ignore them (when you delete them: You should make a backup of the files before...)

Sub MyCheckboxes()
    Dim cb As CheckBox
    For Each cb In ActiveSheet.CheckBoxes  ' Change this to access the correct worksheet.
        If CheckIfThereIsACheckboxOnTop(ws, cb) Then
            Debug.Print "I am hidden: " & cb.Name, cb.Value, cb.ZOrder
            ' cb.Delete  ' Activate to get rid of it.
        Else
            Debug.Print "I am on top: " & cb.Name, cb.Value, cb.ZOrder
            ' Do whatever you want to do with the checkBox
        End If
    Next
End Sub

Function CheckIfThereIsACheckboxOnTop(ws As Worksheet, cbToCheck As CheckBox)
    ' Return True if there is another Checkbox above the current
    Dim cb As CheckBox
    
    For Each cb In ws.CheckBoxes
        If cbToCheck.TopLeftCell.Address = cb.TopLeftCell.Address _
        And cbToCheck.ZOrder < cb.ZOrder Then
            CheckIfThereIsACheckboxOnTop = True
            Exit Function
        End If
    Next
End Function

I have to leave it up to you to translate this to Python code, however, I think you get the idea.

Just for information: Same is true also for other form controls (eg optionButtons or Buttons). It is true for all shapes, however, to check the zOrder of any shape, you need to use the property zOrderPosition.