CheckBox question

[Guest]

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

 

[Juan Pablo González]

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

 

[Ron de Bruin]

Dave Peterson posted this

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

 

[Guest]

Both solutions offers did not work!

 

[Juan Pablo González]

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

 

[Guest]

they were created using the Control Toolbox.
Sheldon

 

[Juan Pablo González]

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

 

[Guest]

Sir,

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

Can similiar code be used for optionbuttons?

Thank you

dtn

 

[Bob Phillips]

'-----------------------------------------------------------------
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)