Check status of checkboxes added dynamically

[Fatema]

I have added checkboxes to one column say A dynamically from another program.
On clicking the header of that column I want to find out the value of each checkbox.(Checked or unchecked).
I am handling it in Worksheet_SelectionChange event. Please provide the vba code for the above.

 

[Andrew Poulsom]

This is tricky because the object model is tortuous, but here's one way:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Obj As Shape
    If Target.Columns.Count > 1 Then Exit Sub
    If Target.Column <> 1 Then Exit Sub
    If Target.Rows.Count < Target.EntireColumn.Rows.Count Then Exit Sub
    For Each Obj In ActiveSheet.Shapes
        If Obj.TopLeftCell.Column = 1 Then
            If Obj.Type = msoOLEControlObject Then
                Obj.Select
                If TypeName(Selection.Object) = "CheckBox" Then
                    MsgBox Selection.Name & " is " & Selection.Object.Value
                End If
            End If
        End If
    Next Obj
    Range("A1").Select
End Sub

I couldn't get it to work without selecting the CheckBox in the code.

 

[Fatema]

Thank you very much for the code. It was useful. Could you please tell me how to find the row number of the selected checkbox in the for loop. Also I would like to set the corresponding row in another column a value based on the checked status.
Another problem is to sort the column bsed on checkbox status.(All checked rows will come on top).
Thank you in advance.

 

[Andrew Poulsom]

The row number of the selected checkbox is:

Obj.TopLeftCell.Row

To put a value in column B of the same row use:

If Selection.Object.Value = True Then
   Obj.TopLeftCell.Offset(0,1) = 1
Else
   Obj.TopLeftCell.Offset(0,1) = 0
End If

Then you could sort descending columns A and B with column B as the key.