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

Question

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:

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.

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

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!

one can only refer to the code name of the sheet in the workbook in which the code is running so the workbook reference is not allowed. — Scott Craner, Sep 18 '20 at 15:42
To achieve this, you need a slightly different method.... One moment posting an answer. — Siddharth Rout, Sep 18 '20 at 15:49
Very confused why you are worried about the worksheet name - use the codename for everything, right? Might be an XY problem. — BigBen, Sep 18 '20 at 15:50
`Set WS3 = Planilha5` will work. This is what Ben suggested above — Siddharth Rout, Sep 18 '20 at 19:51
@BigBen: yes that looks like the best option. Just waiting for Him to confirm my last comment above... — Siddharth Rout, Sep 18 '20 at 21:13

score 3 · Accepted Answer · answered Sep 18 '20 at 19:31

3

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...

answered Sep 18 '20 at 19:31

Pradeep Kumar

6,836
4
21
47

>BINGO!< This one worked like a charm! Thanks Pradeep, I was really getting mad at this, hehehehehe.... – Rubens Sayegh Sep 19 '20 at 15:47
This is so overkill. If it's in `ThisWorkbook`, then just use `Sheet1`. This is *doubly* a duplicate. – BigBen Sep 19 '20 at 16:28
@BigBen, what you suggested is OK for only those cases where Sheet1 is used directly as an object. In case the sheet name is stored in a string variable, you can't do that. – Pradeep Kumar Sep 21 '20 at 10:07
Yes, but you don't need to loop through all the sheets. " I want to set WS3 to the Planilha5 worksheet." is done by `Set WS3 = Planilha5`, plain and simple. Nowhere in OP does it mention that the sheet *codename* is stored as a string... because it shouldn't be. – BigBen Sep 21 '20 at 12:45
1

@BigBen, I interpreted the question slightly different than how you interpretted it. I think "Sheet1" is stored as a string and I assumed this from the line `Set WS1 = (here I need code to pick the "Sheet1" worksheet to variable WS1) (*)` Seems like I was right as OP mentioned in the comments above that `>BINGO!< This one worked like a charm! ...`. Still, if you disagree, feel free to downvote it (if you havent already done it). – Pradeep Kumar Sep 21 '20 at 13:35
I did not get the assumption based on OP's use of `Set WS1 = WB1.Sheet1`, `Set WS1 = WB1.Sheet1.CodeName`, and `Set WS1 = WB1.Sheets(Sheet1)`... but in any case, the question is closed as duplicate so I'm happy to move on. I see no need for OP to store a codename as a string either, and would highly advise to OP not to do that. – BigBen Sep 21 '20 at 13:36
If I just use [Set WS3 = Planilha5], it doesn't work. That was my first try. Then [Set WS3 = WB1.Planilha5] or [Set WS3 = WB1.Sheets(Planilha5)]... All these tries were unsucessful. – Rubens Sayegh Sep 21 '20 at 21:33
1

What happens when you do `Set ws3 = Planilha5`? What is the error message? That's *exactly* how you should use the codename... so if it's not working, then something's afoot @RubensSayegh. As the linked duplicates show, that is the approach you should take, *not* using the codename as a String, which is an all-around *bad* idea. Or if you really just need the worksheet *name*, then refer to `Planilha5.Name` and skip using a worksheet variable. – BigBen Sep 22 '20 at 17:07
Trying to reproduce the error to post in here, I have changed the code to the original idea... But AFTER using Pradeep Kumar's code to capture the Worksheet's Codename - and that worked A-OK! - when I tried the first idea [Set WS3 = Planilha5]... And this one also worked. No error message!!! Could that be possible that Excel "created" a correlation using the coded routine and then "learned" to reference Worksheets by their Codename? Really, I have tried so many times, with all combinations possible... Well in any case, thanks to all, for my problem was solved! – Rubens Sayegh Sep 23 '20 at 13:48
One thing that popped in my head... Maybe the error was because I was using [Set WS3 = HEALTHY.Planilha5] - where HEALTHY was previously DIM as ThisWorkbook. Even thou the Worksheet belongs to the "local" Workbook, maybe by referencing it caused the error... Could that be it? – Rubens Sayegh Sep 23 '20 at 13:51
1

When you use the codename, you should not have any workbook reference before it. Just `Set WS3 = Planilha5` @RubensSayegh. I reiterate that this answer is overkill (and that the question is a duplicate). – BigBen Sep 23 '20 at 17:49

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

1 Answers1