How rename worksheet CodeName at runtime?

[Juska]

Hi
I have a form with a combobox that contains 1,2,3,4
I need to create a work worksheet at Runtime and assign it the following properties.
Name = "Week" & the value of combobox
CodeName = "wks"& Worksheet name


I get a compile error on the CodeName ( Can't assign to read only property)
Here is my code:
Private Sub CreateWorksheet()
Dim wks As Excel.Worksheet
Set wks = Excel.Sheets.Add
With wks
.Name = "Week" & frmPrinters.cboWeek.Text
.CodeName = "wks" & .Name
End With
End Sub


Please assist

 

[Fluff]

Try

Private Sub CreateWorksheet()

   Dim sht As String
   sht = "Week" & frmPrinters.cboWeek.Text
   
   Sheets.Add.Name = sht
   ThisWorkbook.VBProject.VBComponents(Sheets(sht).CodeName).Name = "Wks" & sht
End Sub

But this won't work if the VBA Project is locked. You'll also have to ensure that you have checked "Trust access to the VBA project object model" in macro settings

 

[Juska]

Hi Fluff
The code looks cool, i will give it a try

Thanks