Excel Hyperlink to run userform based on row

I have a workbook that all data is managed through userforms, no manual input. I wanted to make it easier to edit a row by making one of the colums called "Edit" with this as its formula :

=HYPERLINK("Edit:>8";"Click to Edit")

The "8" is the row id number automatically added when creating the row

I thought this would work, but aparrently the hyperlink address needs to be valid for this event to trigger :

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    If Left(Target.Address, 8) = "Edit:>" Then
        editRow(Right(Target.Adress,9))
    End If    

End Sub

Any workarounds or better ideas?

Solution

You need to insert the hyperlink via Ctrl-K shortcut and it works:

and this code

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    Dim rowNum As Long
    rowNum = CLng(Replace(Replace(Target.Address, "https://row", ""), ".com/", ""))

    MsgBox "You are about to edit row " & rowNum

End Sub

returns

But it gets better -- you don't even need to parse the row number from the URL as Hyperlink object provides Range method that you can use to determine the row where it was clicked:

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

    MsgBox "You are about to edit " & Target.Range.Address

End Sub