Problem with looping through checkboxes in Excel

[direktoren]

Hi

I'm trying to get the following to work;

For x = 1 To 28
For Each CheckBox(x) In sSheet
    If Not CheckBox(x) = True Then
        For i = 6 To Sheet3.Cells(1, 1).End(xlToRight).Column
            If Sheet3.Cells(1, i).Value = CheckBox(x).Caption Then
                Sheet3.Columns(i).EntireColumn.Delete
            End If
        Next i
    End If
Next CheckBox
Next x

however this doesn't work - any alternatives?

[DKenny]

You need to loop through the collection of OLEObjects in the worksheet . I use a naming convention whereby all checkboxes are prefixed with "chk" which allows me to identify the specific type of control.

Dim sSheet As Worksheet
Dim oControl As OLEObject
    
    Set sSheet = Worksheets(1)
    
    For Each oControl In sSheet.OLEObjects
        With oControl
            If Left(.Name, 3) = "chk" _
            And .Object.Value = True Then
                'Rest of your code goes here
            End If
        End With
    Next oControl