Solved: How do I change the name of a sheet in VBA properties with VBA

[Djblois]

I know how to change the name of a sheet manually in properties but can I change it in with code???

[Zack Barresse]

Hi there,

These should work ...

[vba]ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewName"
'or
CallByName ThisWorkbook.VBProject.VBComponents("Sheet1"), "Name", VbLet, "NewName"
'or
Public Sub SetNamedProperty(objTarget As Object, strPropName As String, NewVal As Variant)
With New TLIApplication
.InvokeHook objTarget, strPropName, INVOKE_PROPERTYPUT, NewVal
End With
End Sub
Sub ChangeCodeName()
Call SetNamedProperty(ThisWorkbook.VBProject.VBComponents(ThisWorkbook.Worksheet s(1).CodeName), "Name", "NewName")
End Sub
'or
Dim wksTest As Worksheet
Set wksTest = ThisWorkbook.Worksheets(1)
wksTest.[_CodeName] = "NewName"[/vba]

Make sure that the workbook you want to perform this on is substituted for ThisWorkbook, unless the workbook you want to change is housing the code.

HTH

[OBP]

Yes
Sheets("Sheet1").Name = "Test"
changes the name to Test.
You could have just recorded a macro of you doing this of course.

Did I miss something in that question??

[Djblois]

OBP yes but if you look at the properties for that sheet in the VBA editor its name will still be Sheet1 or whatever sheet number it was. For code I just wrote recently it didn't work unless the two names matched up.

[Bob Phillips]

OBP yes but if you look at the properties for that sheet in the VBA editor its name will still be Sheet1 or whatever sheet number it was. For code I just wrote recently it didn't work unless the two names matched up.

Do you understand the difference between Excel sheet name and the worksheet codename?

[Djblois]

No what is the difference???

[Bob Phillips]

All worksheets have two names, the Excel name, and the codename.

When you go to the VBIDE, and select a worksheet, you will see it is called something like

Sheet1 (Sheet1)

The first is codename, the second is the Excel sheet name. If you change the name of the worksheet in Excel, say to Data, the entry in the VBIDE would then be

Sheet1 (Data)

The benefit of this is that it gives the VBA programmer a handle to the worksheet that will not be affected if a user chooses to change the worksheet name in Excel.

So instead of saying

Worksheets("Data").Range("A1").Value = "xxx"

you can say

Sheet1.Range("A1").Value = "xxx"

and rest assured in the knowledge that it sould always be that name.

You can also change the codename yourself, in the VBIDE if you select the worksheet, and make sure the properties window is open (F4), you will see two name properties, The first, (Name), refers to the codename, the second, Name, refers to the Excel name. You can replace the codename with a name of your choice, it is a good idea to give it a meanigful name.