Method '_CodeName' of object '_Worksheet' failed

[hombibi]

I Have code renaming a worksheet codename that runs find in DEBUG but trips in RUNTIME with:

Runtime error '1004'
Method '_CodeName' of object '_Worksheet' failed

Code is part of an excel template that generates a project environment whenever a new excel workbook is generated ( from the template)

   Set ws_projects_definition = wb.Sheets.Add(After:=wb.Sheets(wb.Sheets.Count))
   ws_projects_definition.name = "projects"
   ws_projects_definition.[_CodeName] = "ws_projects_defintion"

The code trips on the codename change, not on the name change.
I have tried to ws_projects_defintion.activate and .select preceding the code name change to no avail. Anyone an idea what I am overlooking?

 

[jasonb75]

The codename is read only, it's not meant to be changed at runtime.

There are a few threads discussing workaround methods here and on other forums, but the ones I've looked at seem to be saying 'don't try this at home'.

Change Worksheet Codename

I have a command button in a userform that runs this macro: Dim NewLabelName As String 'Create and name new sheet (including codename) ActiveWorkbook.Sheets.Add 'creates new worksheet ActiveSheet.Name = Product_Name_La.Caption 'Renames the Worksheet ActiveSheet.Range("B2") =...

www.mrexcel.com

 

[hombibi]

Thanks Jason, I also refer to

Change Worksheet Codename

I have a command button in a userform that runs this macro: Dim NewLabelName As String 'Create and name new sheet (including codename) ActiveWorkbook.Sheets.Add 'creates new worksheet ActiveSheet.Name = Product_Name_La.Caption 'Renames the Worksheet ActiveSheet.Range("B2") =...

www.mrexcel.com

But maybe this is not so much CodeName related, as the code works fine in Debug mode.

Putting in a messagebox before the codename change reveals that the changed object name is fine, but the code name is empty just before the codename instruction when runtime.

In debug mode the codename is not empty before the change instruction.

 

[jasonb75]

but the code name is empty just before the codename instruction when runtime.

That's probably the reason why it's not meant to be changed at runtime. There are quite often anomalies with vba that work in debug but not runtime and vice versa, especially with workarounds for things that are not meant to be done.

It appears that the method detailed in that thread is a bit hit and miss, I managed to get it working on an older version of excel (2016 if I remember correctly) but office 365 refuses to cooperate, implying that it could be affected by version, security settings, or other unknown factors.

 

[hombibi]

Ok, thanks Jason, I had hoped to change the meaningless CodeNames programmatically to more descriptive names. But I guess could work around if I assign the default codename to a variable instead, or detect name changes of sheets and then write them back or so.
I'll put this question to solved.

 

[hombibi]

For those curious, it seems ( VBA Codenames ) that the problem is not so much in changing the CodeName, the problem is that a newly created worksheet does not have a codename to begin with, untill the project is recompiled which does not happen in runtime.

In my example I create a new worksheet, and than check the codename, which is empty in runtime, but available in debug. This is explained as the project is recompiled on each debug run.

So the solution would be to recompile the project programmatically after generating a new worksheet...

 

[hombibi]

Cried victory too early, no solution yet

 

[hombibi]

SOLVED

   Dim wb as workbook
   Set wb = ThisWorkbook
   Dim ws_projects_definition As Worksheet
   ' generate new worksheet
   Set ws_projects_definition = wb.Worksheets.Add(After:=wb.Sheets(wb.Sheets.Count))
   ' force VBA to generate a Codename
   ws_projects_definition.name = wb.VBProject.VBComponents(ws_projects_definition.name).Properties("Codename")
   ' rename the codename in runtime
   ws_projects_definition.[_CodeName] = "ws_pr_def"

 

[jasonb75]

Thanks for returning with your findings
I'll definitely be keeping this in my list of useful threads to refer back to.