Search code examples
excelvbaworksheet

I need to get the CODENAME (first) of a Worksheet so then I can check its NAME

I have a Workbook with 6 Worksheets.

Imagine "Alpha", "Beta", "Gama", "Delta", "Epsilon", "Zeta".

In this order, they are indexed as Alpha= 1 - Beta = 2... Zeta = 6

BUT, if I move Alpha to a position AFTER Beta, the Index number will also change, so I should not refer to the WS by their index numbers. Now, If the user changes the Worksheet NAME, the code - if setting WS by the worksheet NAME - will also fail. The only thing that is not "normally" changeable by the user is the sheets CODE.NAME.

Again... Codename "Sheet1" - Name "Alpha" - Index "1"

Codename "Sheet2" - Name "Beta" - Index "2"

Codename "Sheet3" - Name "Gama" - Index "3" ... and so on.

Even if I change the sheet's position (index) or the sheet's NAME, if I refer to them by their CODE.NAME, it doesn't matter in what position they are located or what their names are.

So, all I want is picking up the Sheet's NAME, using it's CODE.NAME to get there... Something like this:

Dim WB1 as Workbook
Dim WS1 as Worksheet
Dim WSNAME as String
Set WB1 = ThisWorkbook
Set WS1 = (here I need code to pick the "Sheet1" worksheet to variable WS1) (*)

WSNAME = WS1.Name

Pay attention: The final result should store in WS1 the sheet's NAME ("ALPHA"). If the user has changed that name, it should still work, storing the new name. I must FIRST get the sheet by it's CODE.NAME (unchangeable) so then I could check its NAME.

(*) I've tried...

Set WS1 = WB1.Sheet1
Set WS1 = WB1.Sheet1.CodeName
Set WS1 = WB1.Sheets(Sheet1)
Set WS1 = WB1.Sheets("Sheet1").CodeName

Nothing worked...

But if I use

Set WS1 = WB1.Sheets("ALPHA").CodeName

it works, but then I have the same problem... If the user changes "ALPHA" to "ALFA" everything stops working...

Any help would be greatly appreciated. Thanks in advance!

--- Edited to add images -------------------------------------

My Excel is in Portuguese, so instead of "Sheet1", "Sheet2", ... it creates worksheets as "Planilha1", "Planilha2", "Planilha3"... and these "Planilhas" are named with whatever the user wants...

Look at this image: enter image description here

The CODE.NAME for the highlighted sheet is "Planilha5", and the NAME is "Cardiac variables". I need to find "Cardiac variables" in a table (in another Worksheet), to fetch the LINE NUMBER in that table, where all references for this worksheet are stored. If I do a simple lookup in the table's column where the worksheets names are, and search for "Cardiac variables" it would work A-OK.

enter image description here

See, I find what I'm looking for ("Cardiac variables") in line 15!

enter image description here

But if the user changes the worksheet NAME...

Back to the first image. Look at the code to the right. (HEALTHY was previously DIM as ThisWorkbook) There's an entry that says ESTAABA = "Cardiac variables", and then I set WS3 as being the contents of ESTAABA (I could have done directly...). But I don't want to keep the worksheet's NAME in the code, because if the user changes the TAB name (from "Cardiac variables" to "Cardio VAR" as the example above) it won't work anymore.

BUT, if I lookup in the previous table, where "Planilha5" occurs, all would be fine, regardless of the NAME the user wants to use to the worksheet.

The problem is that I am not being able to set WS3 to be understood as the Planilha5 worksheet. If I say Set WS3 = HEALTHY.Sheets("Cardiac variables") it works. If I say Set WS3 = HEALTHY.Planilha5, or = Planilha5, or = Sheets(Planilha5) it doesn't work.

In resume, I don't want to use "Cardiac variables" (or the other NAMEs of the Worksheets - NAMEs can be altered...) anywhere in my code.

I want to set WS3 to the Planilha5 worksheet. Forget the reference "Cardiac Variables", and forget the worksheet index number, for as the user can change names, he can also change the worksheet's position (by moving or addin/removing previous worksheets).

Thanks for the patience!

Solution

  • I'm not sure if I get your question correctly. But here is what you want as per my understanding.

    Add this function in a Module

    Function GetSheetByCodeName(ByVal CodeName As String) As Worksheet
    Dim ws As Worksheet
    For Each ws In ThisWorkbook.Worksheets
        If ws.CodeName = CodeName Then
            Set GetSheetByCodeName = ws
            Exit Function
        End If
    Next
End Function

    And then you can easily call this function whenever you need to refer to any sheet.

    e.g.

    '-- test function
Sub Test()
    Dim ws As Worksheet
    Set ws = GetSheetByCodeName("Sheet1")
    MsgBox ws.CodeName & " -----  " & ws.Name
End Sub

    And this is what you see...

    sheet by codename