Forum Discussion

JoeEason92's avatar
JoeEason92
Copper Contributor
Jan 24, 2024

Hyperlink copying

I have a sheet with multiple hyperlinks, I have linked them all to the same cell on another sheet and my aim is to populate the target cell with a copy of the text of the hyperlink. I have a macro which works, but only when the hyperlinked cell is highlighted and the macro is run manually in VBA. For some reason I have no option in my right click menu to "assign" a macro on the hyperlink. Is there a main menu option which would give me this option or is there a simple extra macro I could incorporate?

Sub FollowHyperlink()
On Error Resume Next

Dim originalText As String
Dim targetSheet As Worksheet
Dim targetCell As Range
Dim hyperlinksInCell As Hyperlinks

' Store the original active cell
Dim initialCell As Range
Set initialCell = ActiveCell

' Set the target sheet and cell
Set targetSheet = Sheets("MCBs C3-54, C3-92, C3-98")
Set targetCell = targetSheet.Range("Y1")

' Check if there is a hyperlink in the original active cell
Set hyperlinksInCell = initialCell.Hyperlinks
If hyperlinksInCell.Count > 0 Then
' Store the original text of the hyperlink
originalText = hyperlinksInCell(1).TextToDisplay

' Follow the hyperlink
hyperlinksInCell(1).Follow

' Copy the different text of the hyperlink into the target cell
targetCell.Value = originalText

End Sub

Excel
Macros and VBA
  • NikolinoDE's avatar
    NikolinoDE
    Gold Contributor
    Jan 24, 2024

    JoeEason92 

    If you want to run a macro when a hyperlink is clicked in Excel, you can use the Worksheet_FollowHyperlink event. This event is triggered when a hyperlink on a worksheet is followed.

    Here is how you can modify your existing code to work with the Worksheet_FollowHyperlink event:

    1. Open the VBA editor by pressing Alt + F11.
    2. In the Project Explorer window, find the sheet where you want this functionality. Double-click on the sheet to open its code window.
    3. Paste the following code into the code window:

    VBA Code:

    Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
    Dim originalText As String
    Dim targetSheet As Worksheet
    Dim targetCell As Range

    ' Set the target sheet and cell
    Set targetSheet = Sheets("MCBs C3-54, C3-92, C3-98")
    Set targetCell = targetSheet.Range("Y1")

    ' Check if there is a hyperlink in the original active cell
    If Target.Address <> "" Then
        ' Store the original text of the hyperlink
        originalText = Target.TextToDisplay

        ' Copy the different text of the hyperlink into the target cell
        targetCell.Value = originalText
    End If
End Sub

    This code will be triggered whenever a hyperlink is followed on the sheet. It checks if there is a hyperlink in the clicked cell and, if so, copies the text of the hyperlink to the specified target cell.

    Make sure to adjust the target sheet and cell according to your specific requirements.

    Now, you don't need a separate macro to be assigned to the hyperlink manually. The Worksheet_FollowHyperlink event will handle this automatically. The text and steps were edited with the help of AI.

     

    My answers are voluntary and without guarantee!

     

    Hope this will help you.

    Was the answer useful? Mark as best response and Like it!

    This will help all forum participants.

Share