Clearing All check boxes using Macro
 

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

Clearing All check boxes using Macro
 

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." <Anil Kumar wrote in message
...
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

Clearing All check boxes using Macro
 

Thanks a ton Rick,

"Rick Rothstein (MVP - VB)" wrote:

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." <Anil Kumar wrote in message
...
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

Clearing All check boxes using Macro
 

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)

"Anil Kumar N." <Anil Kumar wrote in message
...
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

Clearing All check boxes using Macro
 

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


"Rick Rothstein (MVP - VB)" wrote in
message ...
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." <Anil Kumar wrote in message
...
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