Reset Checkboxes from Control Tool Box

[Robert]

Norman provided the following code which resets the checkboxes from the Forms
Toolbar. Can someone amend this for me to apply to checkboxes from the
Control Tool Box

Public Sub Tester()
ActiveSheet.CheckBoxes.Value = xlOff
End Sub

Thank you

 

[Norman Jones]

Hi Robert,

Try:

'=============>>
Public Sub Tester()
Dim obj As OLEObject

For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MsForms.CheckBox Then
obj.Object.Value = False
End If
Next obj

End Sub
'<<=============

 

[Robert]

Thank you Norman,

 

[Robert]

Hi, Norman, I had attached macros to the CheckBoxes. When the Reset
Macro is executed, the individual macros attached to the CheckBoxes are being
activated aswell. Is there a way to prevent the underlying macros from being
executed.
Thank you.

 

[Norman Jones]

Hi Robert,

Hi, Norman, I had attached macros to the CheckBoxes. When the Reset
Macro is executed, the individual macros attached to the CheckBoxes are
being
activated aswell. Is there a way to prevent the underlying macros from
being
executed.

Try:

'=============>>
Public Sub Tester()
Dim obj As OLEObject
Dim Cbox As MsForms.CheckBox

Application.EnableEvents = False
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MsForms.CheckBox Then
obj.Object.Value = False
End If
Next obj
Application.EnableEvents = True
End Sub
'<<=============

 

[Robert]

No luck, Norman, problem persists. Any further response
will be followed up by me in 14 hours time. Anyway, thank you
for your effort. I shall continue to look out.

 

[Dave Peterson]

Try something like this:

'at the top of the General module

Option explicit
Public BlkProc as boolean

Public Sub Tester()
Dim obj As OLEObject
Dim Cbox As MsForms.CheckBox

blkproc = true
For Each obj In ActiveSheet.OLEObjects
If TypeOf obj.Object Is MsForms.CheckBox Then
obj.Object.Value = False
End If
Next obj
blkproc = false
End Sub

Then for each checkbox, you'll have to check the status of the boolean.

Private Sub CheckBox1_Click()

if blkproc = true then exit sub

'rest of your code here

End Sub

 

[Robert]

Yes,Yes,Yes that did it. Thank you Dave.
Thanks to Norman too.