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 · Accepted Answer · 2018-04-21 00:09:00Z

3

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

If shpBox.Type = msoFormControl Then

answered Apr 21, 2018 at 0:09

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related