CODEWITHSUNDEEP
vbaexcel
Steve Hobbs
Steve Hobbs

Reputation: 131

Excel VBA script clear all checkboxes in column

I'm trying to clear all active X check boxes in a specific range of cells, or by column

It seems like it would be simple as I was able to do it for the entire worksheet with the below code. I'm just not sure how to modify this to only apply for the single column or range of cells.

Sub ClearAllCheckboxes()
  Dim Answer As String
  Dim MyNote As String
  Dim Obj As Object

  MyNote = "This will Clear all CheckBoxes Proceed? "
  Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

  If Answer = vbNo Then
    Exit Sub
  Else
    On Error Resume Next
    For Each Obj In ActiveSheet.OLEObjects
         Obj.Object.Value = False
    Next Obj
  End If
End Sub

Upvotes: 4

Views: 3039

Answers (2)

Tim Williams
Tim Williams

Reputation: 166146

Obj.TopLeftCell.Column

will tell you what column the checkbox is located in. Based on that, you can decide whether you want to change its value or not.

Upvotes: 1

nutsch
nutsch

Reputation: 5962

Try using the topleftcell property:

Sub ClearAllCheckboxes()
  Dim Answer As String
  Dim MyNote As String
  Dim Obj As Object
dim rg as range

set rg=selection

  MyNote = "This will Clear all CheckBoxes Proceed? "
  Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")

  If Answer = vbNo Then
    Exit Sub
  Else
    On Error Resume Next
    For Each Obj In ActiveSheet.OLEObjects
        if not intersect(rg, obj.topleftcell) is nothing then _
         Obj.Object.Value = False
    Next Obj
  End If
End Sub

Upvotes: 1

Related Questions