CODEWITHSUNDEEP
excelvba
Miroslav
Miroslav

Reputation: 13

Uncheck "Checkboxes" in Excel

I have the following code to uncheck all the Checkboxes in sheet.

It doesn't change the boxes. I get the message box confirming that the range is green and therefore the loop should kick in.

Sub Changeboxes()

'Dim cb As CheckBox'

If Sheet1.Range("a1").Value = "green" Then

    MsgBox "The range is green.", vbOKOnly

    For Each cb In Sheet1.CheckBoxes
        cb.Value = True
    Next cb

Else
    MsgBox "The range is NOT green.", vbOKOnly

End If

MsgBox "Checked availability updated.", vbOKOnly

End Sub

Upvotes: 0

Views: 2093

Answers (1)

Nick
Nick

Reputation: 3845

Change this line : cb.Value = True to cb.Value = False to uncheck the boxes.

EDIT

Yes, if the checkboxes are activex objects then the above code will not work. Instead, use the following code:

Sub ChangeBoxes()
Dim obj As OLEObject

If Sheet1.Range("a1").Value = "green" Then

    MsgBox "The range is green.", vbOKOnly

        For Each obj In Sheet1.OLEObjects

            obj.Object.Value = True
        Next

    MsgBox "Checked availability updated.", vbOKOnly

Else

    MsgBox "The range is NOT green.", vbOKOnly

End If

End Sub

I have also moved the last messagebox into the first part of the if statement because it was popping up whether or not the value in A1 was green.

Upvotes: 2

Related Questions