1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to address a tab by number vs. name in VBA

Discussion in 'VBA Macros' started by Eloise T, Aug 9, 2018.

  1. Eloise T

    Eloise T Active Member

    Messages:
    802
    A workbook has 3 tabs labeled as follows:
    tab 1 as Red, tab 2 as Yellow, and tab 3 as Green

    In a VBA I might address the tabs as follows:

    If Worksheet.Name <> "Red" And Worksheet.Name <> "Yellow" And Worksheet.Name <> "Green" Then

    to eliminate addressing those tabs in the VBA. How would the above line look if I wanted to address the tabs as 1, 2, and 3?

    If Worksheet.Name <> "Sheet1" And Worksheet.Name <> "Sheet2" And Worksheet.Name <> "Sheet3" Then

    doesn't work.

    Thank you in advance for your input.
    Last edited: Aug 9, 2018
  2. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    I think Worksheet.Name should be Ws.Name ..
    How did you change the worksheet names? From the tabs .. or you renamed the code name of sheets
  3. Eloise T

    Eloise T Active Member

    Messages:
    802
    1. I thought Ws had to be declared first: e.g. Dim Ws As Worksheet, otherwise I could simply spell out Worksheet, no?

    2. I changed the names of the worksheets at the tabs
    upload_2018-8-9_14-57-41.png
  4. YasserKhalil

    YasserKhalil Active Member

    Messages:
    997
    Yes that's right. Ws is a variable for each worksheet if you intend to loop through all the worksheets .. I think it is better to upload sample of your workbook
  5. Eloise T

    Eloise T Active Member

    Messages:
    802
    I'm just trying to determine how to use numbers vs. names when referring to tabs as in:

    If Ws.Name <> "Red" And Ws.Name <> "Yellow" And Ws.Name <> "Green" Then
    Which does work...using actual tab names...

    vs.

    Trying to address the tabs with strictly numbers...
    If Ws.Name <> "Sheet1" And Ws.Name <> "Sheet2" And Ws.Name <> "Sheet3" Then
    Which doesn't work...

    Neither does...

    If Ws.Name <> Sheet1 And Ws.Name <> Sheet2 And Ws.Name <> Sheet3 Then
    without the quotation marks...

    nor does...

    If Ws.Number <> Sheet1 And Ws.Number <> Sheet2 And Ws.Number <> Sheet3 Then
    Last edited: Aug 9, 2018
  6. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    The question is why this need ?

    But if you want to access a worksheet by its Index property,
    it's just its place # in the worksheets order …
    So adressing it with its index, a worksheet can be renamed
    without any drawback in a former VBA procedure
    but you can't change its order within the worksheets collection.
    Sample for first worksheet : Worksheets(1) {instead of Worksheets("Sheet1")}.

    Another sample : If Ws.Index > 3 Then

    For any worksheet located in the workbook where the procedure stands,
    better than its index, better than its name is to use its CodeName !
    As the worksheet can be renamed and reorder in the collection …

    In this picture, Sheet3 is the CodeName of the worksheet Do-Not-Change :​

    [​IMG]

    If Ws.CodeName > "Sheet3" Then

    Chirag R Raval likes this.
  7. Eloise T

    Eloise T Active Member

    Messages:
    802
    So you're saying the following should work:

    If Ws.Index <> 1 And Ws.Index <> 2 And Ws.Index <> 3 Then

    Correct?
    Last edited by a moderator: Aug 10, 2018
  8. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Yes but logically as yet written :
  9. Eloise T

    Eloise T Active Member

    Messages:
    802
    I'm not sure we're on the same page. Just to clarify:

    If using a name, I can use (which I know works):
    If Ws.Name <> "Red" And Ws.Name <> "Yellow" And Ws.Name <> "Green" Then

    If using numbers, I can use:
    If Ws.Index <> 1 And Ws.Index<> 2 And Ws.Index<> 3 Then

    Correct?
  10. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    Both correct but the last for numbers is futile as mathematically,

    just a simple childish logic, is the same as If Ws.Index > 3 ! …

    [​IMG]
  11. Eloise T

    Eloise T Active Member

    Messages:
    802
    Since these two lines of VBA code are eliminating the named/indexed tabs, shouldn't it be:

    If Ws.Index < 4 Then ...?
  12. Marc L

    Marc L Excel Ninja

    Messages:
    4,253
    [​IMG]
    No as very illogic : imagine a number from 1 to 9,
    if it's <>1 and <>2 and <>3 as you wrote so how could it be < 4 ?‼ :rolleyes:
  13. Eloise T

    Eloise T Active Member

    Messages:
    802
    So if I'm trying to eliminate tabs 1, 2, and 3 from "processing",

    If Ws.Index > 3 Then

    ... will handle it and will process tabs 4 and greater, correct?
  14. Marc L

    Marc L Excel Ninja

    Messages:
    4,253

    Yes try ! As I wrote since post #6, 8, 10 & 12 …

    No matter if you're more confortable with your way.​
  15. Eloise T

    Eloise T Active Member

    Messages:
    802
    I tried it and of course you are correct.

Share This Page