VBA Runtime Error 1004 “Application-defined or Object-defined error” when Checking Form Control Type

Question

I hope you can help me. I already searched unsuccessfully for a solution. In a excel sheet I try to count the activated checkboxes with a macro.

Option Explicit
Public Sub Count_CheckBoxes()
    Dim counter As Integer
    Dim shpBox As Shape
    With Tabelle1
        For Each shpBox In .Shapes
            With shpBox
                If .FormControlType = xlCheckBox Then
                    If .ControlFormat.Value = xlOn Then
                        counter = counter + 1
                    End If
                End If
            End With
        Next
        .Cells(29, 3).Value = counter
    End With
End Sub

When I try to run this code, I get a runtime error 1004 “Application-defined or Object-defined error” indicating the line If .FormControlType = xlCheckBox Then.

My excel file has several sheets and all sheets including the workbook are not protected. I have no skills in macros and I assume it's a really simple error...nevertheless I can't find it.

Thank you for your help.

GSerg GSerg · Accepted Answer · 2018-04-21T00:09:00

Before asking for .FormControlType make sure the shape is a Form control:

If shpBox.Type = msoFormControl Then

VBA Runtime Error 1004 “Application-defined or Object-defined error” when Checking Form Control Type

1 Answers