Codename to Friendly Name

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

It’s too short. I know I’m missing something. The error checking, by the way, is in case an invalid sCode is supplied.

Posted in Uncategorized

6 thoughts on “Codename to Friendly Name

  1. 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

  2. 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)

  3. 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

  4. 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

  5. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *