Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Sheet By Codename
I have a spreadsheet with multiple layers of sheets, where I have used the
Sheet Name to give a Description meaningful to other users, but have a separate codename that uses a numbering system, which should allow a button/macro to step up through the layers. In an earlier version the Sheet Name was linked to the Codename, but I now have too many layers & variations in Sheet names to manage easily. Example of problem and Code below Is anyone able to advise where I need to make some mods for it to work? Thanks very much Kris Example Sheet Structure Sheet Codename Main A Mgmt Processes A_01 Project Activites A_02 Mgmt Proc 1 A_01_01 Proj Act 1 A_02_01 Proj Act 2 A_02_02 Proj Act 2a A_02_02_01 Proj Act 2b A_02_02_02 Sub Go_Home() 'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _ Else Returns to Main Sheet If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) = "A" Then ToSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3) ToSheet.Select Else A.Select End If End Sub The macro then allows user to return to Proj Activities from Proj Act 2, regardless of ordering of sheets or Sheet Name. Of Course the Sheet Naming isnt anything close to being as friendly as that above, but hope it makes it easier to undertand |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Sheet By Codename
At a minimum I'd include
Dim TOSheet as excel.worksheet Set TOSheet = ... -- HTH, Barb Reinhardt "Kris_Wright_77" wrote: I have a spreadsheet with multiple layers of sheets, where I have used the Sheet Name to give a Description meaningful to other users, but have a separate codename that uses a numbering system, which should allow a button/macro to step up through the layers. In an earlier version the Sheet Name was linked to the Codename, but I now have too many layers & variations in Sheet names to manage easily. Example of problem and Code below Is anyone able to advise where I need to make some mods for it to work? Thanks very much Kris Example Sheet Structure Sheet Codename Main A Mgmt Processes A_01 Project Activites A_02 Mgmt Proc 1 A_01_01 Proj Act 1 A_02_01 Proj Act 2 A_02_02 Proj Act 2a A_02_02_01 Proj Act 2b A_02_02_02 Sub Go_Home() 'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _ Else Returns to Main Sheet If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) = "A" Then ToSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3) ToSheet.Select Else A.Select End If End Sub The macro then allows user to return to Proj Activities from Proj Act 2, regardless of ordering of sheets or Sheet Name. Of Course the Sheet Naming isnt anything close to being as friendly as that above, but hope it makes it easier to undertand |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Sheet By Codename
The CodeName is like a constant; you cannot change it (well, you can
but I wouldn't recommend it), and it cannot be referenced by a string, anymore than you can reference a variable by a string identifier of the variable's name. If you need the code name of a worksheet tab name, use something like Dim SName As String SName = "two" Debug.Print Worksheets(SName).CodeName The reverse operation, getting a sheet tab name from a code name, isn't as simple. You have to loop through all the worksheets and examine the code name of each sheet until you find the appropriate code name: Function SheetFromCodeName(CodeName As String) As Worksheet Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then Set SheetFromCodeName = WS Exit Function End If Next WS End Function You can then call this as Sub Test() Dim CName As String Dim WS As Worksheet ' create code name "Sheet1" by any string ' manipulation you want. CName = "sh" & "ee" & "t" & "1" Set WS = SheetFromCodeName(CName) If WS Is Nothing Then Debug.Print "No sheet with codename '" & CName & "'." Else Debug.Print "Sheet '" & WS.Name & _ "' has code name '" & CName & "'." End If End Sub Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 15 Dec 2009 07:58:01 -0800, Kris_Wright_77 wrote: I have a spreadsheet with multiple layers of sheets, where I have used the Sheet Name to give a Description meaningful to other users, but have a separate codename that uses a numbering system, which should allow a button/macro to step up through the layers. In an earlier version the Sheet Name was linked to the Codename, but I now have too many layers & variations in Sheet names to manage easily. Example of problem and Code below Is anyone able to advise where I need to make some mods for it to work? Thanks very much Kris Example Sheet Structure Sheet Codename Main A Mgmt Processes A_01 Project Activites A_02 Mgmt Proc 1 A_01_01 Proj Act 1 A_02_01 Proj Act 2 A_02_02 Proj Act 2a A_02_02_01 Proj Act 2b A_02_02_02 Sub Go_Home() 'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _ Else Returns to Main Sheet If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) = "A" Then ToSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3) ToSheet.Select Else A.Select End If End Sub The macro then allows user to return to Proj Activities from Proj Act 2, regardless of ordering of sheets or Sheet Name. Of Course the Sheet Naming isnt anything close to being as friendly as that above, but hope it makes it easier to undertand |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Sheet By Codename
Maybe something like this will get you closer:
Option Explicit Sub testme() Dim ActWkbk As Workbook Dim ActCodeName As String Dim RevCodeName As String Dim NewSheet As Object Set ActWkbk = ActiveWorkbook ActCodeName = ActiveSheet.CodeName If UCase(Left(ActCodeName, 1)) = UCase("A") _ And Len(ActCodeName) 3 Then RevCodeName = Left(ActCodeName, Len(ActCodeName) - 3) Set NewSheet = FindSheetByCodeName _ (wkbk:=ActWkbk, CDName:=RevCodeName) If NewSheet Is Nothing Then 'beep 'just a beep MsgBox "no parent(?)" Else ActWkbk.Activate 'just in case NewSheet.Select End If End If End Sub Function FindSheetByCodeName(wkbk As Workbook, CDName As String) _ As Object 'any type of sheet Dim iCtr As Long Dim mySheet As Object Set mySheet = Nothing For iCtr = 1 To wkbk.Sheets.Count If LCase(wkbk.Sheets(iCtr).CodeName) = LCase(CDName) Then 'found it Set mySheet = wkbk.Sheets(iCtr) Exit For 'stop looking End If Next iCtr Set FindSheetByCodeName = mySheet End Function Kris_Wright_77 wrote: I have a spreadsheet with multiple layers of sheets, where I have used the Sheet Name to give a Description meaningful to other users, but have a separate codename that uses a numbering system, which should allow a button/macro to step up through the layers. In an earlier version the Sheet Name was linked to the Codename, but I now have too many layers & variations in Sheet names to manage easily. Example of problem and Code below Is anyone able to advise where I need to make some mods for it to work? Thanks very much Kris Example Sheet Structure Sheet Codename Main A Mgmt Processes A_01 Project Activites A_02 Mgmt Proc 1 A_01_01 Proj Act 1 A_02_01 Proj Act 2 A_02_02 Proj Act 2a A_02_02_01 Proj Act 2b A_02_02_02 Sub Go_Home() 'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _ Else Returns to Main Sheet If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) = "A" Then ToSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3) ToSheet.Select Else A.Select End If End Sub The macro then allows user to return to Proj Activities from Proj Act 2, regardless of ordering of sheets or Sheet Name. Of Course the Sheet Naming isnt anything close to being as friendly as that above, but hope it makes it easier to undertand -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Selecting Sheet By Codename
Thanks very much.
Its a good work around. Still a little surprised that I can use Codename.Select to select a sheet but cant swap the Codename for a variable as it always has "" around it. Hopefully, I'll never have enough sheets for the Loop to to take a noticable amount of time Thanks again Kris PS If anyone is curious, have pasted the amendments to my code below: Sub Go_Home() 'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _ Else Returns to Main Sheet Dim WS As Worksheet On Error GoTo NoParentSheet If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) = "A" Then ParentSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3) For Each WS In ThisWorkbook.Worksheets If StrComp(WS.CodeName, ParentSheet, vbTextCompare) = 0 Then ToSheet = WS.Name Exit For End If Next WS Sheets(ToSheet).Select Else A.Select End If Exit Sub NoParentSheet: A.Select End Sub "Chip Pearson" wrote: The CodeName is like a constant; you cannot change it (well, you can but I wouldn't recommend it), and it cannot be referenced by a string, anymore than you can reference a variable by a string identifier of the variable's name. If you need the code name of a worksheet tab name, use something like Dim SName As String SName = "two" Debug.Print Worksheets(SName).CodeName The reverse operation, getting a sheet tab name from a code name, isn't as simple. You have to loop through all the worksheets and examine the code name of each sheet until you find the appropriate code name: Function SheetFromCodeName(CodeName As String) As Worksheet Dim WS As Worksheet For Each WS In ThisWorkbook.Worksheets If StrComp(WS.CodeName, CodeName, vbTextCompare) = 0 Then Set SheetFromCodeName = WS Exit Function End If Next WS End Function You can then call this as Sub Test() Dim CName As String Dim WS As Worksheet ' create code name "Sheet1" by any string ' manipulation you want. CName = "sh" & "ee" & "t" & "1" Set WS = SheetFromCodeName(CName) If WS Is Nothing Then Debug.Print "No sheet with codename '" & CName & "'." Else Debug.Print "Sheet '" & WS.Name & _ "' has code name '" & CName & "'." End If End Sub Cordially, Chip Pearson Microsoft MVP 1998 - 2010 Pearson Software Consulting, LLC www.cpearson.com [email on web site] On Tue, 15 Dec 2009 07:58:01 -0800, Kris_Wright_77 wrote: I have a spreadsheet with multiple layers of sheets, where I have used the Sheet Name to give a Description meaningful to other users, but have a separate codename that uses a numbering system, which should allow a button/macro to step up through the layers. In an earlier version the Sheet Name was linked to the Codename, but I now have too many layers & variations in Sheet names to manage easily. Example of problem and Code below Is anyone able to advise where I need to make some mods for it to work? Thanks very much Kris Example Sheet Structure Sheet Codename Main A Mgmt Processes A_01 Project Activites A_02 Mgmt Proc 1 A_01_01 Proj Act 1 A_02_01 Proj Act 2 A_02_02 Proj Act 2a A_02_02_01 Proj Act 2b A_02_02_02 Sub Go_Home() 'Returns to Section Sheet if ActiveSheet is a Sub-Sheet _ Else Returns to Main Sheet If Len(ActiveSheet.CodeName) 1 And Left(ActiveSheet.CodeName, 1) = "A" Then ToSheet = Left(ActiveSheet.CodeName, Len(ActiveSheet.CodeName) - 3) ToSheet.Select Else A.Select End If End Sub The macro then allows user to return to Proj Activities from Proj Act 2, regardless of ordering of sheets or Sheet Name. Of Course the Sheet Naming isnt anything close to being as friendly as that above, but hope it makes it easier to undertand . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using CodeName when Selecting Multi-Tabs | Excel Programming | |||
Hide sheet via codename | Excel Programming | |||
Use of sheet codename in codeline - How? | Excel Programming | |||
change sheet codename | Excel Programming | |||
Selecting a sheet by codename | Excel Programming |