How to select cells with checkboxes?

[tomerdr]

Hi
I have two columns in a sheet one with values and one with check box
activex control

value1 checkbox1
value2 checkbox2
value3 checkbox3
:

I would like to collect all values for which the checkbox is checked
I can get the selected checkboxes but how do i refer to the values
column?

Thanks in advance.

Function GetSelectedCheckBoxes(toIndex As Integer)

Const cbName As String = "cbSelectOption"
Const fromIndex As Integer = 1

Dim Index As Integer
Dim Selected As New Collection
Dim cb
Dim controlName As String

For Index = fromIndex To toIndex
Dim theWorkSheet As Worksheet
Set theWorkSheet = ThisWorkbook.ActiveSheet
controlName = cbName & Index
Set cb = theWorkSheet.OLEObjects(controlName).Object
If (cb.Value = True) Then
Selected.Add cb
End If
Next Index
Set GetSelectedCheckBoxes = Selected

End Function

 

[David G]

Take a look at the modifications I made below.
It finds the row based on the top left corner of the checkbox and
assumes where the values are stored is column 1.

Function GetSelectedCheckBoxes(toIndex As Integer)

Const cbName As String = "cbSelectOption"
Const fromIndex As Integer = 1

Dim Index As Integer
Dim Selected As New Collection Dim cb As OLEObject
Dim controlName As String

For Index = fromIndex To toIndex
Dim theWorkSheet As Worksheet
Set theWorkSheet = ThisWorkbook.ActiveSheet
controlName = cbName & Index

Set cb = theWorkSheet.OLEObjects(controlName)

If (cb.Value = True) Then

Selected.Add theWorkSheet.Cells(cb.TopLeftCell.Row, 1).Value

End If
Next Index
Set GetSelectedCheckBoxes = Selected

End Function

I haven't fully tested it, so let me know if it doesn't work as
expected.

Cheers,
David