Hyperlink

[Thomas Kuriakose]

Respected Sirs,

Is it possible to hyperlink workbook from a data validation list.

We have list of Vendors with price lists and are saved in individual workbooks. We need to link the Vendors in another workbook which has a cell data validated with the respective Vendors.

On selection of Vendor1 the hyperlink should link to Vendor1 workbook, similarly on selection of Vendor2, the hyperlink should link to Vendor2 workbook and follows.

Kindly find attached a the summary workbook from where we want this to work.

Your support and guidance is always appreciated,

with regards,
thomas

 

[Chihiro]

You could use Hyperlink() formula in cell beside it. Using selected value to construct your link.


For VBA same concept applies. But you'd use Workbooks.Open(path).

 

[Thomas Kuriakose]

Respected Sir,

Thank you so very much for this insight and your guidance.

Much appreciated,

Kindly guide on a sample VBA concept if you have the time,

Thank you very much once again,

with regards,
thomas

 

[Chihiro]

So assuming you are storing link in same table like sample.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim fPath As String
If Target.Count > 1 Then Exit Sub
On Error GoTo ErrHandle:
If Not Intersect(Target, [A2]) Is Nothing Then
    fPath = Application.Index(Range("VendorList[Path]"), Application.Match(Target.Value, Range("VendorList[Vendors]"), 0))
    Workbooks.Open (fPath)
End If
ErrHandle:
If Err.Number <> 0 Then
    MsgBox "Unable to open file, check that file exists"
End If
End Sub

 

[Thomas Kuriakose]

Respected Sir,

Thank you very much for the vba input also.

Much appreciated,

with regards,
thomas