Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Parent worksheet.name?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Parent worksheet.name?
You need to set the Parent of a range:
Sub ordinate() MsgBox (Selection.Address) MsgBox (Selection.Parent.Name) Selection.Parent.Name = "rick" End Sub -- Gary''s Student - gsnu200756 "Rick S." wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Parent worksheet.name?
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.) Rick S. wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Parent worksheet.name?
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) "Rick S." wrote in message ... 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Rename Parent worksheet.name?
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 "Dave Peterson" wrote: 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.) Rick S. wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating child worksheet from parent | Excel Worksheet Functions | |||
Using information from one worksheet, to rename inserted worksheet | Excel Worksheet Functions | |||
why can't I rename my worksheet? | Excel Discussion (Misc queries) | |||
rename worksheet | Excel Discussion (Misc queries) | |||
Detect macro's parent workbook or worksheet | Excel Programming |