How to select cells with checkboxes?

  • Thread starter Thread starter tomerdr
  • Start date Start date
T

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
 
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
Click to expand...
Set cb = theWorkSheet.OLEObjects(controlName)
If (cb.Value = True) Then
Click to expand...
Selected.Add theWorkSheet.Cells(cb.TopLeftCell.Row, 1).Value
End If
Next Index
Set GetSelectedCheckBoxes = Selected

End Function
Click to expand...

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

Cheers,
David
 
Back
Top