Mismatch sheet index

[ashley12]

Hi everyone,

I was trying to refer to my worksheets by their indexes instead of names. However, the numbers I saw on the hierarchy were different from the actual worksheets I got from the codes. What did I get wrong?

For example, attached picture is the list of my sheets. Here is my test code:

Sub Test()
    Dim ws As Worksheet
      Set ws = ThisWorkbook.Sheets(4)
      MsgBox ws.Name
End Sub

The result is "Database" instead of "Data validation". It happens with all sheet excepts sheet 1.

Thanks in advance.

 

[Alex Blakenburg]

The index is the order they appear in the worksheet, which is not what you are after.
If your code is in the same workbook as the sheets you can use the codename eg
Set ws = Sheet4
but it won't let you create a variable which might be what you are after (does not allow "Sheet" & 4).

What were hoping to achieve by using the index number ?
If its just to make it independent of the name that the user can change, then use the codename.
eg Debug.Print Sheet4.Name
will return Data validation
Ideally you would then use more meaningful code names.

There is more here on not being able to use a variable for a codename (Mr Excel Forum), and if you do need that, on looping through the worksheets to find the sheet with that codename using the syntax - If ws.CodeName = "Sheet" & x Then ...

 

[StephenCrump]

The sheet index and the sheet's codename are two different things:

Sheets(4) is the 4th sheet in the order shown, which you can also refer to as Sheets("Database") (the sheet name) or Sheet12 (the code name)

Sheets(7) is the 7th sheet in the order shown, which you can also refer to as Sheets("Data validation") (the sheet name) or Sheet4 (the code name).

If you wish, you can use the VB editor to change the codename to something more meaningful than Sheet1, Sheet2 etc.

 

[Zot]

You did nothing wrong. The index is counting sheet from left to right. Nothing to do with the number shown

 

[ashley12]

Thanks everyone. The reason I do this is to disregard the change to the sheets' names because people do this. I did think of changing the codenames but don't know how. Where do I click on the editor to do that? I did some google search but no luck.

 

[StephenCrump]

In the VB editor: View/Properties Window, or use the F4 key.

 

[Zot]

Thanks everyone. The reason I do this is to disregard the change to the sheets' names because people do this. I did think of changing the codenames but don't know how. Where do I click on the editor to do that? I did some google search but no luck.

As far as I know there is no way to do that if I understood what you were trying to do.

The sheet index is determined by the sheet's postition in the tab order from left to right. If the person changed the name but not change the tab order, then referring to index is still valid. However, if someone change to sheet position, the macro will not valid anymore.