CODEWITHSUNDEEP
excelvba
Chadi N
Chadi N

Reputation: 441

How to toggle checkboxes that are linked to a specific range?

I have checkboxes that are LINKED to their own respective cells. When I check checkbox1, I want all the checkboxes in a specific range to be checked/unchecked.

Here is what I Used but it's not working. It's giving me error Object variable or With block variable not set.

Sub SelectAll_Click()
    Dim xCheckBox As CheckBox
    Dim rng As Range, cell As Range
    Set rng = Range("B19:B28")

    For Each cell In rng
        If xCheckBox.Name <> Application.ActiveSheet.CheckBoxes("Check Box 1").Name Then
            xCheckBox.Value = Application.ActiveSheet.CheckBoxes("Check Box 1").Value
        End If
    Next cell
End Sub

Thank you

Upvotes: 1

Views: 627

Answers (1)

Robert Todar
Robert Todar

Reputation: 2145

To toggle a checkbox that is linked to a cell you can simply set the cell value (or return value from a formula) to either True or False.

Using your example it would look something like:

Sub SelectAll_Click()
    Dim xCheckBox As CheckBox
    Dim rng As Range, cell As Range
    Set rng = Range("B19:B28")

    For Each cell In rng
        cell.value = True
    Next cell
End Sub

If you need logic based on the checkbox itself then you would instead loop the checkboxes rather than the a range.

Private Sub demoLoopingCheckboxes()
    Dim control As OLEObject
    For Each control In ActiveSheet.OLEObjects
        With control
            ' The type of activex control
            ' Use this is a if statement to limit to only "CheckBox"
            Debug.Print TypeName(.Object)
            
            ' The cell Address to the linked cell
            Debug.Print .LinkedCell
            
            ' Can read/write the value to the checkbox itself
            Debug.Print .Object.Value
        End With
    Next control
End Sub

Upvotes: 2

Related Questions