Loop Through Array of Shape Names

[talkinggoat]

I am trying to loop through an array list of shapes, but I keep getting the error "Subscript out of range" when the function, ShapeExists is encountered. The code runs fine if I execute the ShapeExists function, by itself. What am I doing wrong?

 Sub Workbook_Open() 
    Debug.Print "Begin Workbook_Open sub." 
    Dim ws As Worksheet 
    Dim wsCount As Integer 
    Dim shapeList As Variant 
    Dim element As Variant 
     
    CheckFirstRun = True 
     
    wsCount = Sheets.Count 
    Debug.Print "Number of Sheets: " & wsCount 

    shapeList = Array("exportData", "InitializeData")

        Debug.Print "Making sure the Initilize and Export buttons are hidden." 
        For i = 1 To wsCount 
            For Each element In shapeList 
                Debug.Print "Working to hide shape " & element 
                If ShapeExists(element) = True Then 
                    Debug.Print element & " exists. Working to hide." 
                    HideShapes (element) 
                End If 
            Next element 
            'Sheets(i).Shapes("exportData").Visible = False 
            'Sheets(i).Shapes("InitializeData").Visible = False 
        Next i  
    Debug.Print "End Workbook_Open sub." 
End Sub 
  
Sub HideShapes(shapeName As Variant) 
    Sheets(i).Shapes(shapeName).Visible = False 
End Sub 
  
Function ShapeExists(shapeName As Variant) As Boolean 
    Dim sh As Shape 
    For Each sh In Sheets(i).Shapes 
        If sh.Name = shapeName Then ShapeExists = True 
        Debug.Print ShapeExists 
    Next sh 
End Function

 

[BSALV]

just a guess, make a string of your element

       If ShapeExists(cstr(element)) = True Then

 

[talkinggoat]

The issue seems to have been that the two functions couldn't see the for variable "i" I made the variable public by using

Public i as Integer

.

 

[JGordon11]

I would simplify.

Put this in a regular module

Sub HideShapes(ShapeList As String) 'ShapeList is a comma separated text string with names of the shapes to hide
    Dim ws, sh, ShapeListCount As Long, ct As Long
    ShapeListCount = Len(ShapeList) - Len(Replace(ShapeList, ",", "")) + 1
    For Each ws In Worksheets
        For Each sh In ws.Shapes
            If InStr(1, ShapeList, sh.Name) Then
                ct = ct + 1
                sh.Visible = False
                If ct = ShapeListCount Then Exit Sub
            End If
        Next
    Next
End Sub

and put this in the ThisWorkbook module:

Sub Workbook_Open()
    HideShapes ("exportData,InitializeData")
End Sub