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.
 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,168,037
Messages
5,856,967
Members
431,843
Latest member
Malahhai

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