Forum Discussion

itsMonty's avatar
itsMonty
Brass Contributor
May 22, 2025

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

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 cod...
excel
Macros and VBA
itsMonty's avatar
itsMonty
Brass Contributor
May 23, 2025

This worked great. 

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? 

 

 

HansVogelaar's avatar
HansVogelaar
MVP
May 23, 2025

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.

Resources