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

  • 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

Eloise T

Active Member
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:

YasserKhalil

Well-Known Member
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
 

Eloise T

Active Member
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
 

YasserKhalil

Well-Known Member
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
 

Eloise T

Active Member
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:

Marc L

Excel Ninja
How to address a tab by number vs. name in VBA
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 :​



If Ws.CodeName > "Sheet3" Then

 

Eloise T

Active Member
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:

Eloise T

Active Member
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?
 

Marc L

Excel Ninja
Both correct but the last for numbers is futile as mathematically,

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


 

Eloise T

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

If Ws.Index < 4 Then ...?
 

Marc L

Excel Ninja

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:
 

Eloise T

Active Member
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?
 

Marc L

Excel Ninja

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

No matter if you're more confortable with your way.​
 
Top