Find checkboxes and then check them

[ExcelMonkey]

I am trying to loop through all the checkbox controls in a spreadsheet and
make sure they are all checked. Why is this not workin:

Sub FindCheckboxes()
Dim obj As OLEObject
For Each obj In OLEObjects
If TypeOf obj.Object Is msforms.CheckBox Then
obj.Object.Value = True
End If
Next obj
End Sub

Thanks

EM

 

[Nigel]

If the code is not behind the actual sheet you need to explicitly reference
the sheet

 

[Rick Rothstein \(MVP - VB\)]

Your code seems to work fine... as long as you are using ActiveX CheckBox'es
(that is, ones you got from the Visual Basic Toolbar). If you got your
CheckBox'es from the Form's Toolbar, try this code...

ActiveSheet.CheckBoxes.Value = True

Rick

 

[Nigel]

sorry code to do it......

Sub FindCheckboxes()
Dim obj As OLEObject
With Sheets("Sheet1")
For Each obj In .OLEObjects
If TypeOf obj.Object Is MSForms.CheckBox Then
obj.Object.Value = True
End If
Next obj
End With
End Sub

 

[ExcelMonkey]

Thanks to both of you. One last question. Is it possible to extract the
location of the objects. More specifically, can you extract which cell
address or which row/col address the object is located by?

Thanks

RK