How to populate cells in a column with hyperlinks to themselves

Question

I would like Column AF populated with hyperlinks to themselves up to the last row containing data in column A.

I will then use this link to run the macro that i need and use Target.Cell.Row to get information from this row within the macro.

I have tried to use the code in this answer https://stackoverflow.com/a/33114213/6893569 but i keep getting the error every time i try to run my macro from with this function or call another macro from within it. I have later found that functions cannot call other macros and can only return values.

Any other ideas? I have been looking for over an hour now and trying a different combination of things, including right clicking on the cell, creating hyperlink and using

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) to launch the macro and Target.Range.Row to get the row number of clicked cell. Then only problem is that i need the hyperlinks (Over 60,000 rows worth) to be auto populated and linked to them selves, it would be ridulcous to do this manually and cant see a way to do this automaticaly unless it was pasting the formula hyperlink in but this cant be used to run the macro for some reason.

Have you tried recording a macro of converting one cell to a hyperlink to itself and then trying to modify the code from there? — urdearboy, Feb 20 '19 at 14:25
Although I'm wondering if there is a better event to launch the macro. — urdearboy, Feb 20 '19 at 14:28

Gary's Student · Answer 1 · 2019-02-20T15:05:08.100

Give this a try:

Sub Macro1()

    Dim AFrange As Range, r As Range, s As String

    Set AFrange = Range("AF1:AF" & Cells(Rows.Count, "A").End(xlUp).Row)

    For Each r In AFrange
        ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:= _
        ActiveSheet.Name & "!" & r.Address(0, 0), TextToDisplay:="myself"
    Next r
End Sub

EDIT#1:

Based on the comment from Pᴇʜ, use this instead:

Sub Macro1()

    Dim AFrange As Range, r As Range, s As String

    Set AFrange = Range("AF1:AF" & Cells(Rows.Count, "A").End(xlUp).Row)

    For Each r In AFrange
        ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:= _
        "'" & ActiveSheet.Name & "'!" & r.Address(0, 0), TextToDisplay:="myself"
    Next r
End Sub

It should enclose the sheet name in apostrophes `SubAddress:="'" & ActiveSheet.Name & "'!" & r.Address(0, 0)` just in case the sheet name contains spaces. — Pᴇʜ, Feb 20 '19 at 14:59

score 1 · Answer 2 · answered Feb 20 '19 at 15:03

target in your event is of type Hyperlink and has no Range property. It has a Address and a SubAddress-property - in case a Hyperlink points to a place within the same Workbook, you find the address in target.SubAddress - however, that is a string and not a Range.

When you set the destination address to the cell itself, you could use ActiveCell.Row to get the row - note that when the address is pointing to a different place, Excel will jump to that place first and executes the code after.

To set the Hyperlinks, use Gary's Student code (I was about to post the same).

Your sentence "I have later found that functions cannot call other macros and can only return values" makes no sense to me - you have an event routine that is a Sub (not a Function). Plus you can call any other routine from both a Function or a Sub.

The overall question is if it is not better to use different Worksheet events like Worksheet_BeforeRightClick or Worksheet_BeforeDoubleClick - there you have a Range as target and no need to blow your worksheet with 60K of Hyperlinks

How to populate cells in a column with hyperlinks to themselves

2 Answers2