CODEWITHSUNDEEP
vbaexcel
Krishn
Krishn

Reputation: 863

Change Sheet code name

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

Upvotes: 8

Views: 22216

Answers (1)

Aleksey F.
Aleksey F.

Reputation: 761

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.

Upvotes: 9

Related Questions