VBA Excel - Opening a link after a lookup check-CodePudding

I have a bit of code that looks like this :

Private Sub Worksheet_Change(ByVal Target As Range)

   Dim searchFolder As String, fileName As String

   Static PowerPointApp As Object

     If Target.Column = 3 Then

       If Target.CountLarge > 1 Then Exit Sub

       If Target.Value = "" Then Exit Sub

With Application
Msg = .IfError(.VLookup(Target.Text, Worksheets("Test").Columns("A:B"), 2, 0), "")

If Msg <> "" Then MsgBox Target.Value & vbLf & vbLf & Msg, vbInformation, "Suggestion d'inspection"

End With
End If
End Sub

What it does is to check a cell's content from Col A in Sheet 1 against a list from Sheet 2, Col 1 ; if there's a match, then a Msgbox will give info from the resulting cell's offset value from Col 2.

I'm trying to adapt this into another function that would do the same check, but then instead of displaying a message from the offset cell, it would open an hyperlink from that same cell.

As an example : I put "Info X" in A:1 Sheet1 ; it checks in Col A Sheet 2 and if it has a match (ie. in A45) it will open the link from B45.

How could I adapt my code to follow those requirement ?

EDIT : I'm a relative rookie in VBA, I may need time to grasp some concepts ^^'

CodePudding user response：

First check if the returned value from the vlookup is not equal to "". If not, then pass that value into the first argument of ActiveWorkbook.FollowHyperlink.

Edit: Remove the two lines about msg and replace them with:

hlink = .VLookup(Target.Text, Worksheets("Test").Columns("A:B"), 2, 0)

If hlink <> "" Then
    ActiveWorkbook.FollowHyperlink (hlink)
End If

You may need to declare hlink as a string variable.