There is a way to change the .CodeName property using VBA. To do so, you have to follow these steps:
You must enable Trust Access to the VBA Object Model. To do this,
- Open your Excel application
- Click File -> Options -> Trust Center -> Trust Center Settings
- Click Macro Settings
- Check the box next to “Trust Access to the VBA project object model”
Note, the article linked above shows how to this step programmatically if you need to, as well.
Open your VBA editor and add a reference to "Microsoft Visual Basic for Applications Extensibility 5.3" via the Tools > References menu.
Run the following macro:
ActiveWorkbook.VBProject.VBComponents(Sheets("Sheet1").CodeName).Name = "YourNewCodeName"
This macro will change the CodeName property of Sheet1 to the string "YourNewCodeName." Some people claim this only works if you've previously opened your VBE (visual basic editor). I haven't experienced this, but just in case you do, here's an alternative macro that should do the trick.
Dim wbk As Workbook
Dim wks As Worksheet
Set wbk = ThisWorkbook
Set wks = wbk.Sheets("Sheet1")
Debug.Assert wbk.VBProject.Name <> vbNullString
.[_CodeName] = "YourNewCodeName"
Take a look and see how it works for you!