I have many, many, many check boxes on a worksheet. Under certain conditions
when the sheet is activated, I need to uncheck ALL the checkboxes. Without
naming each checkbox individually How can I do this in a loop?

thanks

Try this

Dim CB As OLEObject

For Each CB In ActiveSheet.OLEObjects
If TypeName(CB) = "CheckBox" Then
CB.Object.Value = False
End If
Next CB

--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
I have many, many, many check boxes on a worksheet. Under certain
conditions
when the sheet is activated, I need to uncheck ALL the checkboxes.
Without
naming each checkbox individually How can I do this in a loop?

thanks

Dave Peterson posted this

And if you used checkboxes from the Forms toolbar (on a worksheet):
ActiveSheet.CheckBoxes.Value = xlOn 'xlOff



--
Regards Ron de Bruin
http://www.rondebruin.nl


"Juan Pablo González" wrote in message ...
Try this

Dim CB As OLEObject

For Each CB In ActiveSheet.OLEObjects
If TypeName(CB) = "CheckBox" Then
CB.Object.Value = False
End If
Next CB

--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
I have many, many, many check boxes on a worksheet. Under certain
conditions
when the sheet is activated, I need to uncheck ALL the checkboxes.
Without
naming each checkbox individually How can I do this in a loop?

thanks

Both solutions offers did not work!

"Sheldon" wrote:

I have many, many, many check boxes on a worksheet. Under certain conditions
when the sheet is activated, I need to uncheck ALL the checkboxes. Without
naming each checkbox individually How can I do this in a loop?

thanks

Ok, I missed one thing in the previous code. This should work ok:

Sub Uncheck()
Dim CB As OLEObject

With Sheets("Sheet1")
For Each CB In .OLEObjects
If TypeName(CB.Object) = "CheckBox" Then
CB.Object.Value = False
End If
Next CB
End With
End Sub


--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
they were created using the Control Toolbox.
Sheldon

"Juan Pablo González" wrote:

Ok, which toolbar did you use to create the checkbox ?

--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
Both solutions offers did not work!

"Sheldon" wrote:

I have many, many, many check boxes on a worksheet. Under certain
conditions
when the sheet is activated, I need to uncheck ALL the checkboxes.
Without
naming each checkbox individually How can I do this in a loop?

thanks

Sir,

Can the same code be used if the checkboxes are on a userform?

Can similiar code be used for optionbuttons?

Thank you

dtn

"Juan Pablo González" wrote:

Ok, I missed one thing in the previous code. This should work ok:

Sub Uncheck()
Dim CB As OLEObject

With Sheets("Sheet1")
For Each CB In .OLEObjects
If TypeName(CB.Object) = "CheckBox" Then
CB.Object.Value = False
End If
Next CB
End With
End Sub


--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
they were created using the Control Toolbox.
Sheldon

"Juan Pablo González" wrote:

Ok, which toolbar did you use to create the checkbox ?

--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
Both solutions offers did not work!

"Sheldon" wrote:

I have many, many, many check boxes on a worksheet. Under certain
conditions
when the sheet is activated, I need to uncheck ALL the checkboxes.
Without
naming each checkbox individually How can I do this in a loop?

thanks

'-----------------------------------------------------------------
Private Sub ClearCheckboxes()
'-----------------------------------------------------------------
Dim ctl As msforms.Control

For Each ctl In Me.Controls
If TypeName(ctl) = "CheckBox" Then
ctl.Value = False
End If
Next ctl

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"dtn" wrote in message
...
Sir,

Can the same code be used if the checkboxes are on a userform?

Can similiar code be used for optionbuttons?

Thank you

dtn

"Juan Pablo González" wrote:

Ok, I missed one thing in the previous code. This should work ok:

Sub Uncheck()
Dim CB As OLEObject

With Sheets("Sheet1")
For Each CB In .OLEObjects
If TypeName(CB.Object) = "CheckBox" Then
CB.Object.Value = False
End If
Next CB
End With
End Sub


--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
they were created using the Control Toolbox.
Sheldon

"Juan Pablo González" wrote:

Ok, which toolbar did you use to create the checkbox ?

--
Regards

Juan Pablo González

"Sheldon" wrote in message
...
Both solutions offers did not work!

"Sheldon" wrote:

I have many, many, many check boxes on a worksheet. Under
certain
conditions
when the sheet is activated, I need to uncheck ALL the
checkboxes.
Without
naming each checkbox individually How can I do this in a loop?

thanks