Sheet Name References in VBA

[AnneD]

It seems that in the Project Explorer window, each worksheet in a file has two names: One which the system assigns when a new sheet is created and one that user can specify as a tab name. For example, Project Explorer will display: Sheet 1 (user-defined sheet name). I would like to refer to Sheet 1, instead of the user-defined name, so that the code will work even if the sheet name is changed later. Is there a way to refer to the system-assigned name instead of the user-specified name?

[Leif]

You can refer to it by it's number as in 1 = Sheet 1.
This should demonstrate it:

Sub test()
For n = 1 To 3
Worksheets(n).Activate
Next
End Sub

If you want to refer to the tab name itself, enter it within quotes:

Worksheets("Sheet1").Activate

Post modified after reading Legare's more accurate reply below...

[AnneD]

Thanks for the replies and the info.

[LegareColeman]

One small correction. Index number 1 only refers to the original Sheet1 if that sheet is still on tab1. In Sheet1 has been dragged to another position, or deleted, then index 1 will be whatever sheet is currently on tab 1.

[DaveHawley]

Actually you can reference Worksheets by the name Excels assigns to them (which can be seen in the Project Explorer). This is known as the CodeName and is a property of all Excel Worksheets. You would refer to a sheets code name like:
Sheet1.Activate

This is the prefered method when coding in Excel as the Code Name will not change, unlike the Index number and Tab name.

[Andrew Cronnolly]

Even though the CodeName seems to be read only as far as VBA is concerned, it can be changed in the properties window, (its the first name in brackets), in which case Sheet1.Activate will fail. But it is independent of the name that appears on the sheet tab.

Andrew C

[DaveHawley]

Yes it can! When I said It would not change I meant via the User Interface. Where as the the Index number and tab name can be.

[LegareColeman]

Dave: Thanks!!!! I now have a LOT of code to modify. I had seen Sheet1, Sheet2, etc. in the Object Browser numerous times but just skipped over it without making the connection.

[LegareColeman]

<P ID="edit"><FONT SIZE=-1>Edited by LegareColeman on 01/03/19 09:11.</FONT></P>Edited to say look at Dave Hawlety's reply to this this post that shows how this can be done.

No, unfortunately you can not reference the sheet by the name Execl initially assigns to it. I think that would be a good enhancement, but it can't be done today. The only choices you have are the name on the tab, or the sheet's index number which also changes if the sheet's position on the tabs changes. If you can't depend on the sheet name not being changed, then my best suggestion would be to put something in a cell in the sheet to identify it, protect that cell, and then loop through the sheets looking for that cell.

[Andrew Cronnolly]

There is just one (at least) caveat to be aware of. If you delete Sheet1, which is also CodeName Sheet1, and add a new sheet later it will be given the CodeName Sheet1.

Andrew

[DaveHawley]

Legare, don't forget you can use Edit>Replace for this!

Andrew, that is true if the Workbook is saved after deleting the sheet and before adding a new one. But when all is said and done it is by far the safest way to reference Sheets in VBA.

[AnneD]

From Australia to Newfoundland, we live and learn. Thank you.

[Andrew Cronnolly]

Dave , I agree 100% about method of referencing sheets in VBA.

With XL2000, I can (and just checked) delete say sheet1, Insert a new sheet and it is given CodeName Sheet1, in an unsaved workbook. I just think it would probably be better if Excel did not reuse the CodeName, once applied. Perhaps the most foolproof way of keeping track of your sheets is to give them all a new CodeName at design time. But of course we can't do that for any sheets created by VBA.

Andrew

[DaveHawley]

I too am using Excel 2000. If I delete a sheet code named "Sheet1" (Or any code name) and then insert a new sheet it Excel assigns a code name of "Sheet4". It is only if I save after deletion and before inserting the new sheet that it re-uses the code name "Sheet1".

Are you saying this in not the case on your PC ? Strange if it isn't as without saving and clearing memory Excels should assume it still has a Sheet1.

[Andrew Cronnolly]

Dave, I have checked it again and it behaves as I outlined earlier. However I did some experiments and found that when I inserted a new sheet before deleting sheet1 then everything behaved as you say. Now I normally open with just 3 sheets, so I thought that may have some bearing. So I removed my usual template and used the defaults, and guess what, your scenario prevailed. It is very strange, so I am attaching a copy of the file that behaves weirdly, if you would like to track down the cause. Though it will probably behave itself on your PC. There are no macros attached.

Let me know how it behaves on your PC.

Andrew