Nigel wants a hyperlink to another sheet based on the sheet’s Codename, rather than what’s displayed on the tab. The tab can be changed by the user is the issue here, I guess. As part of that, this is a function I came up with to do the conversion. I didn’t really look around for one, so if there’s a better one out there, let me know.
Function CodeToFriendly(sCode As String, wb As Workbook) As String
On Error Resume Next
CodeToFriendly = wb.VBProject.VBComponents(sCode).Properties(“Name”)
End Function
On Error Resume Next
CodeToFriendly = wb.VBProject.VBComponents(sCode).Properties(“Name”)
End Function
It’s too short. I know I’m missing something. The error checking, by the way, is in case an invalid sCode is supplied.
This is the shortest solution to the problem but it will bomb on any copy of Excel 2002 or higher where access to the VBProject has not been trusted under the Tools/Macro/Security/Trusted Sources menu.
Since this is the default you’re pretty likely to run into it. Here’s a project protection safe version that I use in my code:
Public Function szSheetTabName( _
ByRef wkbProject As Excel.Workbook, _
ByRef szCodeName As String) As String
Dim wksSheet As Excel.Worksheet
For Each wksSheet In wkbProject.Worksheets
If wksSheet.CodeName = szCodeName Then
szSheetTabName = wksSheet.Name
Exit For
End If
Next wksSheet
End Function
Rob Bovey
Trusted Projects! That’s what I was missing. Even with a lot of sheets, I’ll bet your function doesn’t take very long. Thanks.
Include the following code in the ThisWorkbook class-file of a Workbook
The function returns the worksheet for a certain codename.
Public Function WorkSheetsByCodeName(ByVal CodeNameStr As String) As Worksheet
On Error Resume Next
Set WorkSheetsByCodeName = Nothing
If Len(CodeNameStr)
Include the following code in the ThisWorkbook class-file of a Workbook
The function returns the worksheet for a certain codename.
Public Function WorkSheetsByCodeName(ByVal CodeNameStr As String) As Worksheet
On Error Resume Next
Set WorkSheetsByCodeName = Nothing
If Len(CodeNameStr) = 0 Then Exit Function
Dim Wsh As Worksheet
For Each Wsh In ThisWorkbook.Worksheets
If StrComp(Wsh.CodeName, CodeNameStr, vbTextCompare) = 0 Then
Set WorkSheetsByCodeName = Wsh
Exit For
End If
Next Wsh
Set Wsh = Nothing
Err.Clear
End Function
The WorkSheetsByCodeName-function is used in a similar way the WorkSheets-function is used.
ThisWorkbook.WorkSheets(“RapportSheet”).Activate
becomes
ThisWorkbook.WorkSheetsByCodeName(“RapportSheet”).Activate
Easy and nice looking code, isn’t it?
Johan
Rather than fuss with the code name of the sheet to make the link work even if the user changes the sheet name, consider using a HYPERLINK function together with CELL:
=HYPERLINK(“[C:some foldermyFile.xls]” & CELL(“address”,Sheet2!$A$1),”Friendly name for my link”)
If the file hasn’t been saved yet, then the formula would be:
=HYPERLINK(“[myFile.xls]” & CELL(“address”,Sheet2!$A$1),”Friendly name for my link”)
And if the link is within the same workbook, then the formula is:
=HYPERLINK(CELL(“address”,Sheet2!$A$1),”Friendly name for my link”)
A macro to create this kind of formula is here: http://vbaexpress.com/kb/getarticle.php?kb_id=237
I have been searching for this “hyperlink with in one workbook” and every time i find the same solution as mentioned by Mr. Brad Yundt (And if the link is within the same workbook, then the formula is:
=HYPERLINK(CELL(“address”,Sheet2!$A$1),”Friendly name for my link” )
my problem is, this hyperlink does not work (i.e when i click it, it does not direct me to desired sheet it gives me this error message “Cannot open the Specified file”)…
I can apply hyperlink by using right click and it works perfectly, but then I have to repeat the process 288 times to link all the sheets (so thats not perfect either)..
I need to make an index of all my sheets so that I can go to any of those by clicking the specified hyperlink.
please help me with this.