Following Hyperlink from a Shape Event

[craig.penny]

I use this code to add shapes with hyperlinks

Public Sub AddHyperlinks2()
      
      Dim TargetRng As Range: Set TargetRng = Application.InputBox(prompt:="Select Range", Type:=8)
      Dim shp As Shape
      
      With ActiveSheet
            Set shp = .Shapes.AddShape(msoShapeRoundedRectangle, 100, 100, 10, 10)
            With shp
                  .Fill.ForeColor.RGB = RGB(204, 255, 204)
                  .Line.ForeColor.RGB = RGB(0, 0, 0)
            '      .TextFrame.Characters.Text = strRowName
                  .TextFrame.HorizontalAlignment = xlHAlignLeft
                  .TextFrame.Characters.Font.ColorIndex = xlAutomatic
                  .TextFrame.Characters.Font.FontStyle = "Bold"
                  .Locked = True
            End With
            .Hyperlinks.Add Anchor:=shp, Address:="", SubAddress:="'" & CStr(TargetRng.Parent.Name) & "'!" & CStr(TargetRng.Address), ScreenTip:="Link"
      End With
End Sub

Which I mostly got from another thread on this site. The problem I'm having is that when using the link on the shape it no longer triggers this Event

Public Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink) 'Sh is caller
      If InStr(1, CStr(Target.Address), "www") > 0 Then Exit Sub
      Call SausageLinks("WBFollow", Sh, Target)
End Sub

So Sub SausageLinks never gets called. My question is this, is there a way I can detect that the link has been followed from the shape?

As always, I truly welcome and appreciate any and all ideas!

 

[RoryA]

Do you actually need the shape to have a hyperlink? Could you not assign a macro to the shape that follows the hyperlink and runs your code?

 

[craig.penny]

Boy, maybe, but I'm just not sure how I could do that in an efficient manner. The way I'm using this is that I have a workbook with about 300 tabs and a UserForm that navigates and is used to make changes. One of the things it can do is to insert a link to another tab and the reason I want to trigger an event is because it would call this sub

Public Sub SausageLinks(ByRef CallerID As String, Optional Sh As Worksheet, Optional Target As Hyperlink)
Static LinkReturnPage As Worksheet
Static LinkTargetPage As Worksheet
Static TargetPageVisible As Boolean
Dim TempString As String, i As Integer, flag As Boolean
If CallerID = "WBFollow" Then
      Set LinkReturnPage = Sh
      i = 2
      Do
            If Mid(Target.SubAddress, i, 1) <> "'" Then
                  TempString = TempString & Mid(Target.SubAddress, i, 1)
            Else
                  flag = True
            End If
            i = i + 1
      Loop Until flag
      If Sheets(TempString).Visible = False Then
            TargetPageVisible = False
            Sheets(TempString).Visible = True
            Sheets(TempString).Select
      Else
            TargetPageVisible = True
      End If
      Set LinkTargetPage = Sheets(TempString)
      Navigate.LinkReturn.Caption = "Link Return"
End If
If CallerID = "LinkReturnButton" Then
      LinkReturnPage.Select
      Navigate.LinkReturn.Caption = ""
      If TargetPageVisible = False Then
            On Error Resume Next
            LinkTargetPage.Visible = False
            TargetPageVisible = True
            On Error GoTo 0
      End If
End If

Which saves the calling location of the link so when a button is pressed on the UserForm, it takes you back to where you were and, if the target sheet was hidden, rehides it. The way I had been trying to do this was to add a link off to the side of any used cells but that often caused an offset of far too many columns to be practical. Then I tried to add the link in the same cell as normal text but learned that's not possible. THEN I stumbled on that code above (which, I think, was from one of your posts, so thanks!!) and it's perfect except I just can't detect it being used.

Sorry for the long response...

Thanks for taking the time to help!

 

[craig.penny]

Perhaps it would make my question more to the point if I asked it this way. When I am in the ThisWorkbook module, the procedure drop down list has a Workbook_SheetFollowHyperlink which just doesn't get called and I don't see anything else on the list that would apply. Is there a way I could accomplish what I'm trying to do if I create a custom object or are the events identical to what's available in the ThisWorkbook module? Or am I just altogether way off in pursuing this?

Thanks again

 

[RoryA]

You cannot create your own event that will be fired by built-in objects (well, you could raise an event from a macro assigned to the shape, but that wouldn't achieve anything).

I think your best bets are either to use a macro assigned to the shape, or to use hyperlinks in cells instead of shapes.

 

[craig.penny]

Okay I'll try to do it by assigning a macro to the shapes. Thanks for keeping me from a pointless endeavor. I appreciate your help!