Clearing All check boxes using Macro

[Anil Kumar N.]

Hi,

I'm trying to uncheck all the check boxes using a macro.

Private Sub Clearall()
Dim ws As Worksheet
Dim chkbx As OLEObject

For Each chkbx In ws.OLEObjects
chkbx.Object.Value = False
Next chkbx
End Sub

and I'm getting an error Run time error-"91":
Object variable or With block variable not set.

Please Help

 

[Rick Rothstein \(MVP - VB\)]

This code will clear any and all the CheckBoxes on the ActiveSheet...

Sub ClearAllCheckBoxes()
Dim ChkBox As OLEObject
With ActiveSheet
.CheckBoxes.Value = False
For Each ChkBox In .OLEObjects
ChkBox.Object.Value = False
Next
End With
End Sub

If you want to specify a particular worksheet, change the ActiveSheet
reference in the With statement to the worksheet you want to specify.

Rick

 

[Anil Kumar N.]

Thanks a ton Rick,

This code will clear any and all the CheckBoxes on the ActiveSheet...

Sub ClearAllCheckBoxes()
Dim ChkBox As OLEObject
With ActiveSheet
.CheckBoxes.Value = False
For Each ChkBox In .OLEObjects
ChkBox.Object.Value = False
Next
End With
End Sub

If you want to specify a particular worksheet, change the ActiveSheet
reference in the With statement to the worksheet you want to specify.

Rick

 

[Bob Phillips]

You need to set ws to something, such as activesheet

Private Sub Clearall()
Dim ws As Worksheet
Dim chkbx As OLEObject

Set ws = ActiveSheet
For Each chkbx In ws.OLEObjects
chkbx.Object.Value = False
Next chkbx
End Sub



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick Rothstein \(MVP - VB\)]

Looks like I missed one test condition... my routine needs an

On Error Resume Next

statement to handle the possibility that no Form's CheckBoxes exist on the
sheet...

Sub ClearAllCheckBoxes()
Dim ChkBox As OLEObject
With ActiveSheet
On Error Resume Next
.CheckBoxes.Value = False
For Each ChkBox In .OLEObjects
ChkBox.Object.Value = False
Next
End With
End Sub

Rick