PDA

View Full Version : [SOLVED] Pass Sheet Codename as argument to Subroutine



MrRhodes2004
12-03-2016, 12:50 PM
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
12-03-2016, 01:36 PM
'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
12-03-2016, 01:54 PM
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
12-03-2016, 04:35 PM
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
12-04-2016, 03:09 AM
p45cal, thanks.