Re: Code to find true Sheetname
Great replies, and quick. It got me part of the way there. What I'm trying to do is check to see if the sheetname is changed by a user, and if so to change a menu name on a 'Contents' page accordingly. My strategy is this:
1. On Workbook Open to declare a public constant PrevShtName and assign it the value of "Sheet1", which is my 'Contents' page and probably won't get renamed.
2. On each of the other worksheets, set the value of PrevShtName = ActiveSheet.Codename as the sheets are selected.
3. In a Workbook SheetDeactivate event, check the final PrevShtName.Name against the contents menu string, and if it has changed, to change the contents menu string to match.
A. Does it look like the above might work? If so,
B. Where and when do I declare the PrevShtName variable and should it be a variable or constant?
C. Should it be a variant or a string? Public or Private?
D. I originally planned only to check the value of 5 sheets, and hard-coded the following in the WorksheetDeactivate event, and it worked well. However I thought it might be best to give the users the ability to rename other sheets as well.
TabName = Sheet10.Name 'User2 sheet name change
MenuName = Sheet1.Range("E8").Value
MenuNameLen = Len(Sheet1.Range("E8").Value) - 4
MenuName = Right(MenuName, MenuNameLen)
If MenuName <> TabName Then
Sheet1.Range("E8").Value = "18. " & TabName
E. I thought instead of hard-coding each of the 20 other sheets like this that I could simplify the coding into either a select case (Select PrevShtName), or even a short routine which would cycle through the sheets somehow. I'm also wondering if it might not be best to create a table with CodeName and Name values for each of the sheets.