Private sub to generate Text

[howard]

I have hyperlinks on sheet "Data" in G17 to G23. I tried to write a Private sub to generate text "Select Macro on sheet" In Col I in the same Row as where the hyperlink has been selected



For eg If I click ion G18 , then this text to be generated in I18

I have written code but cannot get it to generate the text in Col I in the same row as the hyperlink tht has been clicked

Kindly amend my code

 Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim rng As Range
    Set rng = Range("G17:G23")  


    If Not Intersect(Target, rng) Is Nothing And Target.Hyperlinks.Count > 0 Then
        ' If yes, update the corresponding cell in column I with the message
        Cells(Target.Row, "I").Value = "Select Macro on sheet
    End If
End Sub

 

[mumps]

This works for me when I click the hyperlink:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("G17:G23")) Is Nothing And Target.Hyperlinks.Count > 0 Then
        Cells(Target.Row, "I").Value = "Select Macro on sheet"
    End If
End Sub

 

[howard]

Thanks for the help, but I think my hyperlink formula my be causing the issue

See link to my sample data below

Macro to Click on Hyperlink and Generate Text.xlsm

Shared with Dropbox

www.dropbox.com

 

[mumps]

Click here to download your file. I'm not sure how you created the hyperlinks but they were creating the problem. I re-created the hyperlinks using the "Hyperlink" button on the "Insert" tab in the menu at the top and the macro works properly.

 

[howard]

Thanks for your help. I used a formula to generate a hyperlink, but it caused the issue

 

[mumps]

You are very welcome.