I am using this VBA code to automatically check specific checkboxes on an Excel worksheet:

'Listing all shapes in a worksheet object variable named 'sht'
For Each oShp In sht.Shapes
    If IsShapeCheckBox(oShp) Then
    'Only Checkboxes type shapes
        With oShp.TopLeftCell
            If .Column = ColIsForTest And .Row = lRow Then 'found specific checkbox
                oShp.Select 'selecting it (for convenience in step by step testing mode)
                oShp.OLEFormat.Object.Value = True
                Exit For
            End If
        End With
    End If
Next oShp

Although all checkboxes are selected by oShp.Select, oShp.OLEFormat.Object.Value = True works for only one shape.

I also tried:

Application.ActiveSheet.CheckBoxes(oShp.OLEFormat.Object.Name).Select              
Application.ActiveSheet.CheckBoxes(oShp.OLEFormat.Object.Name).Value = True

oShp.OLEFormat.Object.value is set to true, but the checkbox is not checked.

I wonder why this is working for one shape, and not the others. (I suppose the other shapes have been created by copy/paste.)

I also checked, there are no grouped shapes.

I am using this VBA code to automatically check specific checkboxes on an Excel worksheet:

'Listing all shapes in a worksheet object variable named 'sht'
For Each oShp In sht.Shapes
    If IsShapeCheckBox(oShp) Then
    'Only Checkboxes type shapes
        With oShp.TopLeftCell
            If .Column = ColIsForTest And .Row = lRow Then 'found specific checkbox
                oShp.Select 'selecting it (for convenience in step by step testing mode)
                oShp.OLEFormat.Object.Value = True
                Exit For
            End If
        End With
    End If
Next oShp

Although all checkboxes are selected by oShp.Select, oShp.OLEFormat.Object.Value = True works for only one shape.

I also tried:

Application.ActiveSheet.CheckBoxes(oShp.OLEFormat.Object.Name).Select              
Application.ActiveSheet.CheckBoxes(oShp.OLEFormat.Object.Name).Value = True

oShp.OLEFormat.Object.value is set to true, but the checkbox is not checked.

I wonder why this is working for one shape, and not the others. (I suppose the other shapes have been created by copy/paste.)

I also checked, there are no grouped shapes.

• excel
• vba
• checkbox

4 Answers 4

Refactored Code:

Dim CheckBox As Excel.CheckBox
For Each CheckBox In sht.CheckBoxes
    With CheckBox.TopLeftCell
        If .Column = ColIsForTest And .Row = lRow Then
            CheckBox.Value = True
            Exit For
        End If
    End With
Next

Referencing Form Controls on a Worksheet

Here is how we can iterate over the `Worksheet.Shapes` collection and get typed references to the checkboxes:

Dim Shape As Shape, CheckBox As Excel.CheckBox
For Each Shape In sht.Shapes
    If TypeName(Shape.OLEFormat.Object) = "CheckBox" Then
        Set CheckBox = Shape.OLEFormat.Object
        CheckBox.Value = True
    End If
Next

But it is much simpler to iterate over the Worksheet.CheckBoxes collection like this:

Dim CheckBox As Excel.CheckBox
For Each CheckBox In sht.CheckBoxes
    CheckBox.Value = True
Next

Referencing ActiveX Controls on a Worksheet

Using the Worksheet.OLEObjects collection to iterate over the ActiveX controls on the worksheet removes a level of extraction. I setup my example to show how we can get typed references to the CheckBoxes.

Dim Item As OLEObject, CheckBox As MSForms.CheckBox
For Each Item In sht.OLEObjects
    If TypeName(Item.Object) = "CheckBox" Then
        Set CheckBox = Item.Object
        CheckBox.Value = True
    End If
Next

Here is how we would have to drilldown to get typed references to the CheckBoxes using the Worksheet.Shapes collection:

Dim Shape As Shape, Item As OLEObject, CheckBox As MSForms.CheckBox
For Each Shape In sht.Shapes
    If TypeName(Shape.OLEFormat.Object) = "OLEObject" Then
        Set Item = Shape.OLEFormat.Object
        If TypeName(Item.Object) = "CheckBox" Then
            Set CheckBox = Item.Object
            CheckBox.Value = True
        End If
    End If
Next

Updated as per rotabor comment.

The solution

It seems to me what this statement Exit For causes the behavior you claimed. Also, this condition .Column = ColIsForTest And .Row = lRow narrows the quantity of affected CheckBoxes (generally, it means CheckBoxes may not be ordered in the Shapes collection by neither 'TopLeftCell.Column' no 'TopLeftCell.Row', which can cause Exit For prematurely).

Based on the problem statement I can recommend you this code:

For Each oShp In sht.Shapes
  If IsShapeCheckBox(oShp) Then
    'Only Checkboxes type shapes
    With oShp.TopLeftCell
        If .Column = ColIsForTest And .Row <= lRow Then ' all checkboxes above
          oShp.OLEFormat.Object.Value = True
        End If
      End With
  End If
Next oShp

Additional information

Also, there are two worksheet controls sets: Form and AxtiveX. To have the code working with both of them use:

          On Error Resume Next
          oShp.OLEFormat.Object.Value = True
          oShp.OLEFormat.Object.Object.Value = True
          On Error GoTo 0

The correct command for this

oShp.OLEFormat.Object returns an OLEObject object and on it
oShp.OLEFormat.Object.Object returns the CheckBox object.

For Each oShp In sht.Shapes
    If IsShapeCheckBox(oShp) Then
    'Only Checkboxes type shapes
        With oShp.TopLeftCell
            If .Column = ColIsForTest And .Row = lRow Then 'found specific checkbox
                oShp.Select 'selecting it (for convenience in step by step testing mode)
                oShp.OLEFormat.Object.Object.Value = True  'modified
                Exit For
            End If
        End With
    End If
Next oShp

Thank you all for your answers. Unfortunately, none of these worked in my case, but this helped me in refactoring my code.

I finally found the solution : there were many checkboxes in superposition inside each XL cells. Since only the first found was checked, and then exiting the loop, the new check was hidden by all above checkboxes.