1

I've got a lot of hyperlinks and I want to assign a macros to each of them and Worksheet_FollowHyperlink captures only Inserted Hyperlinks but not the HYPERLINK() function. So I want my Inserted Hyperlinks refer to nothing so when I press them nothing happens. Or I want them to refer themselves. But when I just copy one to another cell it still refers to its parents cell. So I have to edit a new one so it refers to its new cell. And I've got hundreeds of hyperlinks to be copied and edited as well. I need that because I don't want the hyperlinks skip me to the parent hyperlink's cell.

Thanks in advance

Denis Vakula
  • 51
  • 1
  • 5
  • You can just use `#rc` for the hyperlink address - no need for a hidden sheet. – lori_m Oct 17 '15 at 07:51
  • oh, thank you lori_m. Works just perfect. But what it means #rc ? – Denis Vakula Oct 18 '15 at 13:55
  • 1
    The `# `signifies a reference within a document and any formula that returns a reference can follow in either `A1` or `r1c1` notation. Here `rc` means this cell in `r1c1`notation. Entering `#indirect(rc)` into the hyperlink dialog will take you to whatever the text entered in the hyperlink cell refers to eg `Sheet1!A1` – lori_m Oct 18 '15 at 20:58
  • Possible answer to initial question: https://stackoverflow.com/questions/62896319/run-excel-macro-with-hyperlink-formula?noredirect=1#comment111224826_62896319 – GuidoT Jul 14 '20 at 13:42

4 Answers4

7

You will be better off using the HYPERLINK() function. You can use it for what you want like this:

=HYPERLINK("#HyperlinkClick()", "Text you want to Display")

Notice the # at the beginning. This is important.

Now create a function called HyperlinkClick:

Function HyperlinkClick()

    Set HyperlinkClick = Selection
    'Do whatever you like here...
    MsgBox "You clicked on cell " & Selection.Address(0, 0)

End Function

Be sure to place this function in a STANDARD CODE MODULE.

That's it.

Excel Hero
  • 14,253
  • 4
  • 33
  • 40
  • Great idea! You've just saved my ass. Thanx – Denis Vakula Oct 14 '15 at 00:15
  • Sorry Excel Hero, it seems to be wrong way. Worksheet_FollowHyperlink still doesn't deal with my/your hyperlinks. Have you got any ideas? – Denis Vakula Oct 14 '15 at 00:45
  • @DenisVakula That is correct. This was never intended to work with Worksheet_FollowHyperlink. This is a different solution. You create your own custom procedure to fire instead. In my answer I named that custom procedure HyperlinkClick() but you can choose any name you like. You do not need Worksheet_FollowHyperlink() at all. Forget about it. – Excel Hero Oct 14 '15 at 01:34
  • @DenisVakula If you don't understand how this works, please ask. Creating hyperlinks to a hidden sheet is not recommended. – Excel Hero Oct 16 '15 at 05:40
  • I am getting a `Reference isn't valid` message box every time I click on the hyperlink. @ExcelHero any idea on why could this be happening? – MarioVW Mar 27 '20 at 00:43
  • @ExcelHero Your formula is awsome. thank you for this. If i may go into more details, if i use this formula:'''=HYPERLINK("#testSheetFormula(BE76:BE77)","Test")''', the VBA formula recolonizes the area correctly. Is there a way that would allow me to send the target range by selecting the area in a formula, something like cell("address",) but for multiple cells? – Dumitru Daniel Apr 15 '20 at 10:24
  • @MarioVW make sure you include this line: `Set HyperlinkClick = Selection` – Filcuk Jul 27 '23 at 15:59
1

I've just founded a solution. If I refer my Inserted Hyperlink to some cell in other sheet and then make it very hidden (xlSheetVeryHidden), it works just perfect. Now my hyperlinks refer to the Neverland and the macro captures them as well. Thank you all for your patiense.

Denis Vakula
  • 51
  • 1
  • 5
1

Good solution Excel Hero but not for everything: I try to make a kind of outline but it's impossible to hide a row in the function: nothing happen! But if a make a direct call to the same code with a button, everything works fine. This is my test:

Function test()

Set test = Selection

 Dim i, j, state As Integer
state = Selection.Value
i = Selection.Row + 1
j = i

If state = "6" Then

    Do Until ActiveSheet.Cells(j, 7).Value = 1 Or ActiveSheet.Cells(j, 4).Value = ""
        j = j + 1
    Loop
    ActiveSheet.Rows(i & ":" & j - 1).EntireRow.Hidden = True
    Debug.Print "test group: " & i & ":" & j - 1
Else
    Do Until ActiveSheet.Cells(j, 7).Value = 1 Or ActiveSheet.Cells(j, 4).Value = ""
        j = j + 1
    Loop
    ActiveSheet.Rows(i & ":" & j - 1).EntireRow.Hidden = False
    Debug.Print "test ungroup: " & i & ":" & j - 1
End If

End Function

My debug.print give me this:

test group: 4:26

Simon Martineau
  • 11
  • 1
0

Select a group of cells and run:

Sub HyperAdder()
   For Each r In Selection
      ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:=r.Parent.Name & "!" & r.Address(0, 0), TextToDisplay:="myself"
   Next r
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99