0 votes
in VBA by Beginner (16 points)
edited by
Error 450 appears with this try:

Worksheets("Sheet1").CodeName = "newCodename"

Any suggestions? Thank you - greetings from vienna, frank.

2 Answers

+3 votes
by Super Expert (2.8k points)
selected by
 
Best answer

There is a way to change the .CodeName property using VBA. To do so, you have to follow these steps:

Step 1

You must enable Trust Access to the VBA Object Model. To do this,

  1. Open your Excel application
  2. Click File -> Options -> Trust Center -> Trust Center Settings
  3. Click Macro Settings
  4. Check the box next to “Trust Access to the VBA project object model

Trust Access to VBA Project Object ModelNote, the article linked above shows how to this step programmatically if you need to, as well.

Step 2

Open your VBA editor and add a reference to "Microsoft Visual Basic for Applications Extensibility 5.3" via the Tools > References menu.

Step 3

Run the following macro:

Sub ChangeCodeName()
ActiveWorkbook.VBProject.VBComponents(Sheets("Sheet1").CodeName).Name = "YourNewCodeName"
Debug.Print Sheets("Sheet1").CodeName
End Sub

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.

Sub ChangeCodeName_alternative()
Dim wbk As Workbook
Dim wks As Worksheet
Set wbk = ThisWorkbook
Set wks = wbk.Sheets("Sheet1")
Debug.Assert wbk.VBProject.Name <> vbNullString
With wks
  .[_CodeName] = "YourNewCodeName"
End With
End Sub

Take a look and see how it works for you!

by Beginner (16 points)
+1
Thank you for the precise and concise explanation, that is perfect and usable for me. frank.
by Beginner (16 points)
Both subs work as expected, although I have no reference to "Microsoft Visual Basic for Application Extensibility 5.3" . It seems it works perfect without it.
0 votes
by Skilled (376 points)
The CodeName property is read-only. But you can change the name on a sheet's tab like this:
Sheet1.Name = "newTabName" or Sheets("oldTabName").Name = "newTabName"
asked Nov 1 in VBA by Beginner (16 points)
reshown Nov 1 by
Is there a way to change the codename of a worksheet?
by Beginner (28 points)

Actually, you can change the CodeName. I just don't know if you can do it programmatically (I'm pretty sure you can, but you have to adjust security/access settings).

See my answer here.

by Super Expert (2.8k points)

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...