Hyperlink not working correctly

Question

I have two hyperlinks in my excel document.

On Sheet1, in cell A1 I have written the formula, =HYPERLINK("#'Sheet1'!A1","click").

On Sheet1, in cell A2 I have done what I think is the same but using a interactive GUI. Right click in cell A2 => Hyperlink => Place in this document => (Type the cell reference) A2.

I also have written a very short sub so that when I click on these hyperlinks I get a message box.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    MsgBox ActiveCell
End Sub

I have placed this macro in Sheet1.

When I click on cell A2 I get a MsgBox with the cell value in. As expected

However, when I click on cell A1 nothing happens.

Why are these two links behaving differently? How can I make cell A1 behave in the same way as cell A2 whilst still using a formula in the cell?

So Why?

I have a program that generates a CSV file. For simplicity the structure looks a little like this.

f1,f2,compare
f3,f2,compare

This means it is easy to overwrite the format.

I then open this file and save it as a .xlsm. What I wanted to have is when the compare is clicked it would run a macro. This could be in the form of a button or a hyperlink or anything else as long as it is obviously clickable.

Hence why I was going for the approach of =HYPERLINK("#'Sheet1'!A1","click") as it was easy to increment the number after the column and still show it was something that was clearly clickable.

Therefore it would be a quick change to get the CSV file output in a format of

f1,f2,"=HYPERLINK(""#'Sheet1'!C1"",""compare"")"
f3,f2,"=HYPERLINK(""#'Sheet1'!C2"",""compare"")"

The goal of this is to have something that is as automated as possible as this workbook could have over a thousand row so it is not feasible for me to manually sort out a compare button on each row

Update 2

When the compare button is clicked, a macro is run. This macro is called CompareFiles. It takes the values from the cells on the same row but in columns a and b, passes them into a shell command, and opens a different program that is used to compare the files.

Sadly only the **Inserted** hyperlink can raise an event; the **formulaic** hyperlink cannot. — Gary's Student, Jun 21 '18 at 15:11
Why the `#`? Also that's a circular reference. What are you trying to link to? — BruceWayne, Jun 21 '18 at 15:11
@BruceWayne The cells purposly refer to themselves and I got the # from this answer https://stackoverflow.com/a/27783477/4601149 — Dan, Jun 21 '18 at 15:14
@Gary'sStudent Well... That is a shame. Is there a way around it, like calling a macro in the url? — Dan, Jun 21 '18 at 15:15
@Gary'sStudent A bit like [this](https://stackoverflow.com/a/33114213/4601149)? Unfortunately with that specific answer, I keep getting a `Reference is not valid` pop up — Dan, Jun 21 '18 at 15:19
Thanks for the link, didn't know that! What if you try `=HYPERLINK("#'Sheet1'!"&"A1","click")`? — BruceWayne, Jun 21 '18 at 15:29
@Dan So your aim is to exploit an hyperlink's event ? What does follow it? Maybe some other event can be used ? Or, assuming you cannot avoid using an inserted link, could you loop an insertion ? — MrDogme, Jun 21 '18 at 17:32
Agreed with @MrDogme - What's your ultimate goal with the hyperlink? I feel like this may be an [XY Problem](https://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) — BruceWayne, Jun 21 '18 at 18:15
@BruceWayne Sorry for the belated response. I have updated the question to show a use case — Dan, Jun 21 '18 at 19:14
@MrDogme Sorry for the belated response. I have updated the question to show a use case — Dan, Jun 21 '18 at 19:14
So when I click cell `C3`, you want a macro to run? What is the macro called? Does [this thread](https://stackoverflow.com/q/28728600/4650297) help? Or [this page](https://www.extendoffice.com/documents/excel/4422-excel-run-macro-from-hyperlink.html)? — BruceWayne, Jun 21 '18 at 19:17
@BruceWayne I have edited the question again. Basically it will take two file paths from column a and b on the same row, then open these two files in a separated comparison tool using a shell command — Dan, Jun 21 '18 at 19:20

Dan · Accepted Answer · 2018-07-19T12:20:02.877

What you can do is select all the cells that need linking to themselves and use a answer by Gary's Student to do it. The original post can be found here

Sub HyperAdder()
    Dim r As Range, s As String
    For Each r In Selection
        If Len(r.Text) = 0 Then
            s = "X"
        Else
            s = r.Text
        End If
        ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:=r.Parent.Name & "!" & r.Address(0, 0), TextToDisplay:=s
    Next r
End Sub

If you are using a .vbs file like myself, in my case to convert the .csv and do a few other things, then you can use something similar to the following.

For Each cell In YourSheet.UsedRange.Columns("C:F").Cells
    If Len(cell.Text) > 0 Then
        ConflictsSheet.Hyperlinks.Add cell, "", "'" & cell.Parent.Name & "'" & "!" & cell.Address(0, 0), cell.Text
    End If
Next

Hyperlink not working correctly

1 Answers1