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 (3.2k 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 Expert (911 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, 2019 in VBA by Beginner (16 points)
reshown Nov 1, 2019 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 (3.2k points)

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

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

...