Trying to obtain the cell address that has a hyper link in-CodePudding

I am using VBA in an Excel workbook to open VLC on a specific music video without getting the MS warning about 'are you sure this is safe'. I have the code to run it, and am attempting to call it from a hyperlink. I don't want to use a button, too many videos, so decided on a hyperlink. The problem I have is the Hyperlink when clicked doesn't make the cell active. I'm using ActiveCell to select the artist and track name (column ActiveCell.row). I can't find anywhere that will give me the cell reference of the hyperlink used. Created a defined Names range for the column of hyperlinks. Since the hyperlink doesn't make the cell active, the code reverts to the first in the range.

Option Explicit

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Call Start_VLC
End Sub

Public Sub Start_VLC()
    Dim strProgName As String
    Dim strPlaceTitle As String
    Dim strLoc As String
    Dim ActCol As String
    Dim ActRow As Double

    strLoc = Range("f1").Value & Range("B" & ActiveCell.row).Value & " - " & Range("C" & ActiveCell.row).Value & ".mp4"    'F1 = MP4 location and B&C title
    strProgName = "C:\Program Files\VideoLAN\VLC\vlc.exe" 'vlc location
    strPlaceTitle = strLoc 'MP4 location
    
    MsgBox "Active Cell = " & ActiveCell.row 'just to keep track
    MsgBox "strLoc = " & strLoc
    MsgBox "strProgName = " & strProgName
    MsgBox "strPlace Title = " & strPlaceTitle

    Call Shell("""" & strProgName & """ """ & strPlaceTitle & """", vbNormalFocus)
End Sub

Any suggestion welcome

CodePudding user response：

Using the Hyperlink.Range property:

Returns a Range object that represents the range that the specified hyperlink is attached to.

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
   Start_VLC Row:=Target.Range.Row
End Sub

Public Sub Start_VLC(ByVal Row As Long)
   strLoc = Range("f1").Value & Range("B" & Row).Value & " - " & Range("C" & Row).Value & ".mp4"  
   ...
End Sub

CodePudding user response：

Please find the total solution I went with. It includes the hyperlink pasting and video calling. It may be longwinded but I'm a happy soul. Cheers BigBen

Option Explicit Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink) Call Start_VLC End Sub Public Sub Start_VLC() Dim strProgName As String Dim strPlaceTitle As String Dim strLoc As String Dim ActCol As String Dim ActRow As Double

strLoc = Range("f1").Value & Range("B" & ActiveCell.row).Value & " - " & Range("C" & ActiveCell.row).Value & ".mp4"    'F1 = MP4 location and B&C title
strProgName = "C:\Program Files\VideoLAN\VLC\vlc.exe" 'vlc location
strPlaceTitle = strLoc 'MP4 location

Call Shell("""" & strProgName & """ """ & strPlaceTitle & """", vbNormalFocus)

End Sub

Sub HyperlinkFill() 'Create hyperlinks to start vVLC video from click Dim LastRow, HyLRow As Long

LastRow = Range("J1")   5 'J1 has the total number of videos available & hyperlinks start on row 6 currently to row 3352

For HyLRow = 6 To LastRow
    ActiveCell.Hyperlinks.Add Anchor:=Sheets("Searching").Range("I" & HyLRow), Address:="", SubAddress:="'Searching'!I" & HyLRow, TextToDisplay:="PLAY NOW"
Next HyLRow

End Sub