Clearing All check boxes using Macro

  • Thread starter Thread starter Anil Kumar N.
  • Start date Start date
A

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
 
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
 
Thanks a ton Rick,

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
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)
 
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
 
Back
Top