Check status of checkboxes added dynamically

Fatema

New Member
Joined
Oct 27, 2002
Messages
3
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.
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
This is tricky because the object model is tortuous, but here's one way:

Code:
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

New Member
Joined
Oct 27, 2002
Messages
3
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
The row number of the selected checkbox is:

Obj.TopLeftCell.Row

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

Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,088
Messages
5,599,664
Members
414,325
Latest member
kfg1287

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top