Change Sheet code name

Question

For some reason the below code only works when run in the vbe, when run in the event handling code, or by macro list the codename remains sheet1.

Please can someone investigate ?

Sub changesheetcodename()    

Dim ws,tsst as worksheet

For Each Ws In ActiveWorkbook.Worksheets
    If Ws.Name <> "Instructions"  then ws.delete
Next Ws

Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = "Sheet1"

set tsst = Sheets("Sheet1")
With tsst
    .Parent.VBProject.VBComponents(.CodeName) _
    .Properties("_CodeName") = "test"
End With

End Sub

Answer 1

Sub change_code_name()
  Dim wbk As Object, sheet As Object
  ActiveWorkbook.VBProject.Name = "VBAProject"
  Set wbk = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.CodeName)
  wbk.Name = "wbk_code_name"
  Set sheet = ActiveWorkbook.VBProject.VBComponents(ActiveWorkbook.Sheets(1).CodeName)
  sheet.Name = "sheet_code_name"
End Sub

Also the access to the VBAProject is required: see "Macro settings" and set "Trust access to the VBA object model". To programmatically change the settings, look here.