Looping through shapes on worksheet-CodePudding

scenario is i have an array of shapes on my worksheet and i want to set a loop running through each shape setting the colour accordingly.

what i've come up with so far:

Dim yFilter(1 To 5) As String
yFilter(1) = "BD_P"
yFilter(2) = "FIN_P"
yFilter(3) = "PM_P"
yFilter(4) = "IPS_P"
yFilter(5) = "ENG_P"


Dim sh As Shape

For Each sh In ActiveSheet.Shapes
    For i = 1 To 5
        If sh = yFilter(i) Then
            sh.Select
            With Selection.ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 255, 255)
            End With
        End If
    Next i
Next

the issue with the above is it's not matching sh.name it's just matching against the group shape however if i use ActiveSheet.Shapes.Range(Array("BD_P")).Select it works fine ....i'd just rather not have to do this for every single item i'd rather have it run through as an array for example.

any ideas how i can resolve this would be appreciated.

CodePudding user response：

Loop Through Shapes From List

Dim yFilter(1 To 5) As String
yFilter(1) = "BD_P"
yFilter(2) = "FIN_P"
yFilter(3) = "PM_P"
yFilter(4) = "IPS_P"
yFilter(5) = "ENG_P"

Dim ws As Worksheet: Set ws = ActiveSheet ' improve!

Dim shp As Shape, i As Long

For i = LBound(yFilter) To UBound(yFilter)
    On Error Resume Next
        Set shp = ws.Shapes(yFilter(i))
    On Error GoTo 0
    If Not shp Is Nothing Then
        shp.Fill.ForeColor.RGB = RGB(255, 255, 255)
        Set shp = Nothing
    End If
Next i

CodePudding user response：

after countless attempts i finally found a way to make it work

incase anyone else ever stumbles upon a similar issue:

yFilter = Array("BD_P", "FIN_P", "PM_P", "IPS_P", "ENG_P")
        For Each Item In yFilter
        ActiveSheet.Shapes.Range(Array(Item)).Select
            With Selection.ShapeRange.Fill
                .Visible = msoTrue
                .ForeColor.RGB = RGB(255, 255, 255)
            End With
    Next