Is there any formula that returns a Sheet Name, or a way to trap for a changed Name?

[foxguy]

I currently have a macro that runs during Worksheet_Activate() that stores the Sheet Name in a Defined Name (and changes it if the Sheet Name has changed).
I then have a formula on a different sheet that points to that Defined Name (there are several formulas on that sheet that point to Defined Names on several sheets).
I then have a macro that reads the Sheet Name from that formula. This macro runs during Worksheet_Calculate().

The problem is if the user changes the Name of the worksheet by right mouse clicking the sheet tab but doesn't activate the sheet the Worksheet_Activate() macro doesn't update the Defined Name and my macro crashes trying to get a Sheet Name that doesn't exist any more.

Is there any formula that returns a Sheet Name, or a way to trap when a user changes the Sheet Name?

[JBeaucaire]

You can put this formula into a cell and it will return the sheet name as long as the file has been saved at least once.

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,256)

[Andrew-R]

The CELL function can be used to return a sheet name:

=MID(CELL("Filename",A1),FIND("]",CELL("Filename",A1))+1,255)

Edited to add: beaten to it - my wife's using my computer and typing in formula on an iPad takes forever

[foxguy]

Thanks, that saves me having to figure out a different method.

[JBeaucaire]

Heh, @Andrew, I cheat. I have a HUGE text file with all the most common "tricks" stored for quick copy/paste.

[mikerickson]

I currently have a macro that runs during Worksheet_Activate() that stores the Sheet Name in a Defined Name (and changes it if the Sheet Name has changed).

Why do you have to update the name with an Activate routine?

If you define a simple named range Name: myCell RefersTo: =Sheet1!$A$1, it will update as the user changes things.

To expand the idea,
Name: mySheetName
RefersTo: =MID(GET.CELL(32,Sheet1!$A$1), FIND("]",GET.CELL(32,Sheet1!$A$1))+1, 255)

will update as the user changes the tab name of Sheet1.

[foxguy]

Mike;
I don't have to update the Defined Name with the Activate method anymore.
I originally used the Activate method so that it would not be volatile. But I overlooked the fact that the user could change the name after activating the sheet and my formula referring to that Defined Name would not work until the user changed sheets and then changed back. So whether I like it or not, I have to use a volatile function to accommodate a user changing the sheet name.

I may still try to think of a way to do it in a non volatile way, but I've pretty much decided that it's not worth the trouble. There's only 1 formula that refers to each sheet's Defined Name, so it's not worth worrying about. I have thought about using the sheet's CodeName, but I don't like having the CodeName visible where a user might see it, and it's just not worth trying to hide it in a way that would prevent the user from ever seeing it.