Refering to codename of worksheet

[tiredofit]

IF I know my workbook has a worksheet called Apples, I can refer to it as follows:

With wb.Worksheets("Apples")

    ' Do something

End With

but I DON'T know for certain there's a worksheet called Apples because the user might change it.


Instead I DO know there's a worksheet with a codename of wksApples.


So how can incorporate this into my code?


This doesn't work:

With wb.Worksheets(wksApples.Codename)


    ' Do something


End With

I don't want to have to do this:

    Dim ws As Worksheet


    For Each ws In wb.Worksheets


        If ws.CodeName = "wksApples" Then


            Exit For


        End If


    Next ws


    With ws


        ' Do something


    End With

Thanks

 

[tiredofit]

You don't need to loop to find a sheet with a given code name

Sub test()
    MsgBox WorksheetCodeNamed("wksApples").Range("A1").Value
End Sub

Function WorksheetCodeNamed(wsCodeName As String, Optional wb As Workbook)
    If wb Is Nothing Then Set wb = ActiveWorkbook
    
    With wb
        Set WorksheetCodeNamed = .Sheets(.VBProject.VBComponents(wsCodeName).Properties("index").Value)
    End With
End Function

Thanks Mike,

What's the significance of "index"?

 

[Fluff]

Speaking of which, any idea why this is the case? It would make some of our lives a whole lot easier if we could refer to the codename of a worksheet in a specific WB.

No idea why, but you can do it if you set a reference to the other workbook. Although I think that means the other workbook will automatically open, whenever you open the "Master"

You don't need to loop.

Agree, but that means you need to enable "Trust access to the VBA Project".

 

[mikerickson]

Thanks Mike,

What's the significance of "index"?

VBComponent("codename").Property("index").Value returns the index of the sheet (i.e. position from the left of the tabs)
VBComponent("codename").Property("name").Value returns the name of the vbComponent, not the tab name of the sheet.

 

[tiredofit]

VBComponent("codename").Property("index").Value returns the index of the sheet (i.e. position from the left of the tabs)
VBComponent("codename").Property("name").Value returns the name of the vbComponent, not the tab name of the sheet.

Thanks again.

Doesn't seem to make a difference for my purposes whether I use "Name" or "Index".