How can I adjust this VBA code to effect the sheet not the workbook

Question

I have this code for clearing checkboxes from an inserted object, but it clears the entire workbook. Does anyone know how to adjust it to clear the sheet it is placed in. Please note I chose this code because the checkboxes are in groups and it was still able to clear the boxes.

Thanks

Sub Oval1719_Click()

Dim sheet As Worksheet
For Each sheet In Sheets
On Error Resume Next
sheet.CheckBoxes.Value = False
On Error GoTo 0
Next sheet

End Sub

hansvogelaar · Answer

Sub Oval1719_Click()
    On Error Resume Next
    ActiveSheet.CheckBoxes.Value = False
    On Error GoTo 0
End Sub
&nbsp;

itsmonty · Answer

This worked great.&nbsp;However I realized the code doesn't work for checkboxes that are grouped together. Sorry for adding a question to this answered thread, but is there a way to include checkboxes that are grouped as well?&nbsp;&nbsp;&nbsp;

hansvogelaar · Answer

If you have one level of groups:
Sub Oval1719_Click2()
    Dim shp As Shape
    Dim shp2 As Shape
    On Error Resume Next
    For Each shp In ActiveSheet.Shapes
        If shp.Type = msoFormControl Then
            If shp.FormControlType = xlCheckBox Then
                shp.ControlFormat.Value = False
            End If
        ElseIf shp.Type = msoGroup Then
            For Each shp2 In shp.GroupItems
                If shp2.Type = msoFormControl Then
                    If shp2.FormControlType = xlCheckBox Then
                        shp2.ControlFormat.Value = False
                    End If
                End If
            Next shp2
        End If
    Next shp
End Sub
If you have groups within groups, we'd need a recursive procedure. Let me know.

Forum Discussion

How can I adjust this VBA code to effect the sheet not the workbook

3 Replies

Resources