Rename Parent worksheet.name?

[Guest]

I know how to rename a worksheet in VBA, ***Sheets(i).Name = "Sheet" & i"***,
but I can not seem to rename the parent name.
Using ***Sheets(i).Parent.Name*** errors out. (invalid assignment?)
If I create a worksheet it counts and adds to the parent name so I could end
up with Sheet15(Sheet2) what I want is Sheet2(Sheet2).

Thank you in advance.
--
Regards

Rick
XP Pro
Office 2007

 

[Guest]

You need to set the Parent of a range:

Sub ordinate()
MsgBox (Selection.Address)
MsgBox (Selection.Parent.Name)
Selection.Parent.Name = "rick"
End Sub

 

[Dave Peterson]

The parent of the worksheet is the workbook.

You can't change that workbook's name--well, except by saving it with the new
name.

It kind of looks like you're trying to change the codename of a worksheet.

If that's true, you can use code like:

ThisWorkbook.VBProject.VBComponents("Sheet1").Name = "NewCodeName"
where sheet1 is the code name

or
dim wks as worksheet
set wks = activesheet
ThisWorkbook.VBProject.VBComponents(wks.codename).Name = "NewCodeName"
'or
ThisWorkbook.VBProject.VBComponents(wks.CodeName) _
.Properties("_CodeName").Value = "NewCodeName2"


But you'll have to toggle a security setting
tools|macro|security|trusted publisher tab
check "trust access to Visual basic project"

(added in xl2002 IIRC--who knows where it's hiding in xl2007 <vbg>.)

 

[Chip Pearson]

The Parent of a Worksheet is a Workbook, and a Workbook cannot be renamed.
The only way to change the name of a Workbook is to do a SaveAs to a new
file name. Thus, code like

Dim WS As Worksheet
Set WS = Worksheets(1)
WS.Parent.Name = "abc"

will fail. The Range object has a Parent property that returns the worksheet
containing the range, and that can be renamed:

Dim RR As Range
Set RR = Worksheets(1).Range("A1")
RR.Parent.Name = "New Name"


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Guest]

I see, I was thinking "parent" when I should have been thinking "codename",
yesterday I was getting the name of a hyperlink and not the parent name that
I needed, thus I thought this is similar, yea! Parent name! I'm a genius!
~NOT!
LOL

Thank You; Gary, Dave and Chip!
--
Regards

Rick
XP Pro
Office 2007