Selecting Sheet By Codename

[Kris_Wright_77]

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

 

[Barb Reinhardt]

At a minimum I'd include

Dim TOSheet as excel.worksheet

Set TOSheet = ...

 

[Chip Pearson]

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]

 

[Dave Peterson]

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]

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

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]

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

.