How to add a sheet to an exception list when it can be renamed and moved

Question

I am following up my earlier question where I opted to go with a function and built a list of names of worksheets that are exception to the task being performed

Function exception(Sheet_Name As String) As Boolean
    Dim exceptions(3) As String
    Dim Counter_x As Long
        
    exceptions(0) = "MASTER ITEM LIST"
    exceptions(1) = "ITEM LIST"
    exceptions(2) = "Rebar Protperties"
    exceptions(3) = "Rebar Worksheet"
    
    exception = False
        
    For Counter_x = LBound(exceptions) To UBound(exceptions)
        If Sheet_Name = exceptions(Counter_x) Then
            exception = True
        End If
    Next Counter_x
    
End Function

In this approach the worksheet names are hard coded. I also took an approach at one point for an exception being worksheet("blah").index > 2. Using the index number seems like a bad idea as the worksheet can be moved around which would change its index number. Hard coding the worksheet name in the list also seems like a poor choice as the worksheet name could be renamed.

Is there a way to track the name of the sheet or its index number such that it can be maintained in the exceptions list?

@SJR is code name the `Sheet2` part of `Sheet2 (ITEM LIST)` that I see under Project - VBAProject? — Forward Ed, Mar 05 '19 at 20:45
That's right. See this thread https://stackoverflow.com/questions/41477794/refer-to-sheet-using-codename — SJR, Mar 05 '19 at 20:47
Funny I was just reading that exact thread between my comment and your response. Seems like exactly what I want. Thank you for placing me on the correct path — Forward Ed, Mar 05 '19 at 20:50

score 2 · Accepted Answer · answered Mar 05 '19 at 20:55

You can see if the worksheet name is within the exception list in one pass.

Function exception(Sheet_Name As String) As Boolean
    Dim exceptions(3) As String

    exceptions(0) = "MASTER ITEM LIST"
    exceptions(1) = "ITEM LIST"
    exceptions(2) = "Rebar Protperties"
    exceptions(3) = "Rebar Worksheet"

    exception = iserror(application.match(Sheet_Name , exceptions, 0))

End Function

How to add a sheet to an exception list when it can be renamed and moved

Is there a way to track the name of the sheet or its index number such that it can be maintained in the exceptions list?

1 Answers1