Worksheet Object name change 1

[hrm1220]

I'm hoping someone can help me. I've looked through the threads and could not find what I need. I'm trying to rename the VBAProject Worksheet object. I can manually do this through VBA Editor (view, Properties, and in the name change it there). I'm hoping there is a way to do it through VBA.

Example:
Microsoft Excel Objects
Sheet1(Sheet1)
Sheet2(Sheet2)

would like to to rename to

Microsoft Excel Objects
test(Sheet1)
Sheet2(Sheet2)

Thanks for your help in advance

 

[combo]

Among others, you can use hidden method:

Dim wksTest As Worksheet
Set wksTest = ThisWorkbook.Worksheets(1)
wksTest.[_CodeName] = "NewName"

combo

 

[SkipVought]

FYI,

As a matter of practice, I assign the sheet CodeName on all the sheets in my workbook, and reference accordingly, so there's never any ambiguity.

Skip,

_[red][/red]

 

[TonyJollans]

Amongst others ...

Is there any other way? or are you just playing safe?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[combo]

Hi Tony,
without using more sophisticated solutions:
1.
ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewName"
2.
CallByName ThisWorkbook.VBProject.VBComponents("Sheet1"), "Name", VbLet, "NewName"

combo

 

[combo]

BTW, the "more sophisticated" can be (reference to TypeLib Information):

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.Worksheets(1).CodeName), "Name", "NewName")
End Sub

combo

 

[TonyJollans]

Ah yes, of course! Thank you.

The TLI one is interesting. Is there any occasion where that would be necessary or beneficial?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association

 

[hrm1220]

thank you all for your help. This is what I was looking for. I'm truly grateful for your help and quick response

 

[SkipVought]

combo,

Please elaborate on this concept.

Skip,

_[red][/red]

 

[hrm1220]

I have one more question. Will the code below work for the excel object rename for a different workbook?
code:
ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewName"


I have code in one workbook that is creating information and calculations in another workbook. So the rename is in the other workbook not the original workbook

 

[PHV]

Have you tried to use ActiveWorkbook instead of ThisWorkbook ?
You may also consider playing with an instantiated Workbook object.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886