Clear Checkboxes

[Perry Diels]

Hello,

In an Excel (2003) Sheet I have installed some check boxes. The only thing
they do is show a status (checked/unchecked) Now I want a handy reset button
that resets all checkboxes on the sheet to unchecked. I have installed a
button via the "Control Toolbox" with this code:

Private Sub CommandButton1_Click()
Dim ckBx As CheckBox
For Each ckBx In ActiveSheet.CheckBoxes
ckBx.Value = xlOff
Next ckBx
End Sub

Unfortunately nothing at all happens when clicking on it. Any idea why. Or
is there another (better) method to achieve the same result.

Thanks in advance for you help.
Perry

 

[Dave Peterson]

Any chance your checkboxes were from the controltoolbox toolbar, too (along with
the command button).

If yes, then you get them this way:

Dim OLEObj As OLEObject
For Each OLEObj In Me.OLEObjects
If TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj

By the way, your code worked ok for me using checkboxes from the Forms toolbar.

(if you get an error that says: "User-defined type not defined", you'll need to
set a reference (tools|References) to "Microsoft Froms 2.0 object library".

Or you could just insert a userform and then delete it.

 

[Peo Sjoblom]

One way

Sub Test()
Dim oleObj As Object
For Each oleObj In ActiveSheet.OLEObjects
If TypeOf oleObj.Object Is MSforms.CheckBox Then
oleObj.Object.Value = False
End If
Next oleObj
End Sub

 

[Perry Diels]

Hello Dave,

Thanks for your answer. You are absolutely right, that's my mistake. I admit
I was not aware of the existence of checkboxes in different toolbars. Why
would one use the checkboxes from the forms or the controlbox toolbar? Don't
worry if it is to long to explain, my problem is solved anyway... just
wondering if it is not to much asked.

Thanks a lot for your help.
Best regards,
Perry


-------------------------------------------------------------------------

 

[Dave Peterson]

The stuff from the Forms toolbar is old (pre-xl97).

The controls from the Controltoolbox toolbar have a lot more features. Right
click on one of them and look at all the properties. Doubleclick on one and
look at all the events that you can use.

My opinion is that if you only need the most basic version, stick with the Forms
toolbar controls. I find them easier to use. If you need more "customability",
then use the controls toolbox toolbar controls.

(too many controls, too many tools, too many toos! <bg>)