CODEWITHSUNDEEP
excelvbahyperlink
JC_RMB
JC_RMB

Reputation: 119

Excel Hyperlink Using Sheet CodeName

I have added images to an Excel worksheet and each image has a hyperlink to another worksheet in the same workbook. Unfortunately, if a user renames a destination worksheet, the hyperlink breaks and it currently has to be manually reset. I believe that I can deal with this situation automatically in VBA by deleting the hyperlink and recreating a new one on a Change event. However, is it possible to use the worksheet codename instead of its new name in the Hyperlink so that there's no need to change anything?

Upvotes: 2

Views: 138

Answers (1)

VBasic2008
VBasic2008

Reputation: 54777

Activate a Sheet (Using Its Code Name) By Using a Shape

  • If using hyperlinks is not mandatory, you could assign this macro to each of the pictures (shapes).
Sub ActivateSheet()
    
    ' The following two arrays need to have the same number of elements.
    Dim PictureNames() As Variant
    PictureNames = VBA.Array("Picture 1", "Rectangle 1") ' add more, adjust!
    Dim SheetObjects() As Variant
    SheetObjects = VBA.Array(Sheet2, Chart4) ' add more, adjust!
    
    Dim pName As String
    On Error Resume Next
        ' 'Sheet1' is the code name of the worksheet with the pictures. Adjust!
        pName = Sheet1.Shapes(Application.Caller).Name
    On Error GoTo 0
    If Len(pName) = 0 Then Exit Sub ' not called from a shape on 'Sheet1'
    
    Dim Index As Variant: Index = Application.Match(pName, PictureNames, 0)
    If IsError(Index) Then Exit Sub ' doesn't exist in list (array)
    
    ' The arrays are zero-based, while the index is one-based.
    SheetObjects(Index - 1).Activate

End Sub

Upvotes: 1

Related Questions