CODEWITHSUNDEEP
excelvba
Kutuloo
Kutuloo

Reputation: 33

Loop over Shapes but not Comments

I have a huge Excel file, that I have to evaluate. I'm trying to automate this task. The Excel file contains a lot of shapes (text boxes) and comments. I need to evaluate the text boxes but to ignore the comments. Apparently when I loop over shapes with a for each loop, I also get the comments. Is there a way to ignore the comments. Here is my code:

    For Each shp In ActiveSheet.Shapes
        On Error GoTo NextShape
        If InStr(shp.Name, ", ") Then
            splitName = Split(shp.Name, ", ")
            If splitName(UBound(splitName)) = "FS" Then
                check_codes_in_Textbox shp
            End If
        End If
NextShape:
    If Err.Number <> 0 Then MsgBox (Err.Description)
    Next

In my Excel file a lot of the textboxes have ", FS" in their name, that is why I was trying to use an InStr-Function, but this way is kind of inconvenient.

Is there a way to loop over shapes and not over comments?

Upvotes: 1

Views: 149

Answers (1)

R35P3K7
R35P3K7

Reputation: 96

you can use shp.Type

For Each shp In ActiveSheet.Shapes
    On Error GoTo NextShape
    If InStr(shp.Name, ", ") And shp.Type <> 4 Then 'type 4 is commment, type 17 is text box for example etc... Check with debug.print shp.Type what is current shape type
        splitName = Split(shp.Name, ", ")
        If splitName(UBound(splitName)) = "FS" Then
            check_codes_in_Textbox shp
        End If
    End If
NextShape:
If Err.Number <> 0 Then MsgBox (Err.Description)
Next

Upvotes: 1

Related Questions