Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can one macro serve several check boxes? | Charts and Charting in Excel | |||
Clearing Check Boxes & Running a diff Macro when unchecking the ch | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Discussion (Misc queries) | |||
Clearing Check Boxes and Cells | Excel Worksheet Functions |