Pass Sheet Codename as argument to Subroutine

[MrRhodes2004]

Hey Y'all,

I'm struggling with passing a worksheet codename through the a subroutine. Below is an example of what I have been trying to do. The intent is to have a single function that works on the called sheet.

I'd like to be able to send the sheet codename instead of the name itself as that might get changed.

When I pass the name, the function seems to work properly. However, when I try to use the codename....nope.

Sub tryPassSheetName(wName$)
    Dim ws As Worksheet
    Set ws = ActiveWorkbook.Sheets(wName)
    Debug.Print ws.CodeName
End Sub


Sub testy()
    tryPassSheetName ("Sheet29") 'codename of the sheet (Does NOT work)
    tryPassSheetName ("TL List of Indicators") 'actual name of the sheet (Works only until user changes sheet name)
End Sub

Thoughts?

[Kenneth Hobs]

'Note that you must add trust for vbcomponent access:
'File -> Options -> Trust Center -> Trust Center Setttings -> Macro Settings -> Trust Access to the VBA Project object model.
Sub Test_SheetFromCodeName()
  Dim ws As Worksheet
  Set ws = SheetFromCodeName("Sheet1", ActiveWorkbook)
  MsgBox ws.Name
End Sub


'http://www.excelforum.com/excel-programming-vba-macros/968869-how-to-reference-a-sheet-with-codenames-and-variables.html
'http://www.ozgrid.com/forum/showthread.php?t=188760
Function SheetFromCodeName(aName As String, Optional wb As Workbook) As Worksheet
  If wb Is Nothing Then Set wb = ThisWorkbook
  With wb
    Set SheetFromCodeName = .Sheets(.VBProject.VBComponents(aName).Properties("Index"))
  End With
End Function

[SamT]

Sub Examples()
Dim X As String
X = "Test Sheet" 'The "Tab" name of the first sheet in my workbook

MsgBox Sheets(1).Name
MsgBox Sheets(X).Name
MsgBox Sheet1.Name     'Sheet1 is the name of that Sheet Object and needs no other qualifiers.

MsgBox Sheets(1).CodeName
MsgBox Sheets(X).CodeName
MsgBox Sheet1.CodeName

End Sub

Sub Example2()
Dim Sht As Object

For Each Sht In Sheets
If Sht Is Sheet1 Then
MsgBox Sht.Name
MsgBox Sht.CodeName
End If
Next
End Sub

[p45cal]

Have your Sub accept a sheet object OR a string - if it's a string assume it's the tab name of the sheet, if it's a sheet object treat it accordingly:

Sub tryPassSheetName(wName)
Dim ws As Worksheet
If TypeName(wName) = "String" Then
  Set ws = Sheets(wName)
Else
  Set ws = wName
End If
Debug.Print ws.CodeName, ws.Name
End Sub

Sub testy()
tryPassSheetName Sheet3  'codename of the sheet (Does work)
tryPassSheetName "Sheet2"  'name on the sheet's tab
End Sub

[MrRhodes2004]

p45cal, thanks.