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

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

--

Regards,
Nigel




"ExcelMonkey" wrote in message
...
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

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


"ExcelMonkey" wrote in message
...
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

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

--

Regards,
Nigel




"ExcelMonkey" wrote in message
...
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

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

"Rick Rothstein (MVP - VB)" wrote:

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


"ExcelMonkey" wrote in message
...
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