Write "true" in the adjacent cell after the link is clicked

Well, the goal of the project is to set up a VBA code that writes "true" in the adjacent cell as soon as you click on a link and false if the user has not yet clicked on the link.

I started with a small code, just to know how I was going to proceed:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    If Target.Range.Address = Range("A1").Address Then
        Range("B1").Value = "True"
    End If
End Sub

This code checks to see if the clicked hyperlink is in cell A1, and if it is, it writes "True" in cell B1.
But the macro only works if the link is written directly in cell A1, and I wanted:

1. VBA code works with a link created by the hyperlink function.
2. As long as the user has not clicked on the link, the macro must display "false" in the adjacent cell and "true" otherwise.
3. This macro must be able to be applied to all cells containing hypertext links regardless of how they were created.
4. friendly_name in the hyperlink() has to be different from the address of the link.

To fix that, I did this:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim rng As Range
    Dim matchFound As Boolean
    
    For Each rng In Me.UsedRange
        If rng.Hyperlinks.Count > 0 Then
            If rng.Hyperlinks(1).Address = Target.Address Or rng.Hyperlinks(1).TextToDisplay = Target.TextToDisplay Then
                rng.Offset(0, 1).Value = "True"
                matchFound = True
            Else
                If rng.Offset(0, 1).Value <> "True" Then
                    rng.Offset(0, 1).Value = "False"
                End If
            End If
        End If
    Next rng
    
    If Not matchFound Then
        MsgBox "No matching hyperlink found."
    End If
End Sub

But the macro doesn't work when clicking on hyperlinks created with HYPERLINK() function, the macro should normally display "false" if the user does not click on the link but it does not work either.

Can you help me please?

Here's a way to do it that doesn't rely on Worksheet events, since the FollowHyperlink event doesn't trigger for links created using the HYPERLINK function. I'm assuming that your links are to webpages, but the code could easily be modified to allow for links to other locations in the Workbook, etc.

• Create a user-defined function (UDF) that does two things: sets the value of the adjacent cell to TRUE, and opens the URL provided in the browser
• Link to the function from the HYPERLINK formula, providing the URL in each case.

The function looks like this:

Public Function GoToWebpageAndSetTrue(Webpage_Url)

    Set GoToWebpageAndSetTrue = Selection
    Selection.Offset(0, 1).Value = True
    ActiveWorkbook.FollowHyperlink Webpage_Url

End Function

Should be pretty easy to understand. Insert this code inside a module in the Workbook to define the function.

Now, in your hyperlink cells you can type the following, for example:

=HYPERLINK("#GoToWebpageAndSetTrue(""https://www.google.com"")", "Link 1")

The double quotation marks around the URL are to escape them because they are inside another pair of quotation marks. There's not a lot of documentation online about linking to UDFs from HYPERLINK—I got the method from this answer. Note the caveat in the answer about the importance of setting the function to Selection in the first line.

I created an example Worksheet that looks like this:

After clicking Link 2 (cell A2), my screen looks like this:

which is believe is what you are looking to achieve.