• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Hyperlink

Thomas Kuriakose

Active Member
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
 

Attachments

Chihiro

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

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

Thomas Kuriakose

Active Member
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

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

Code:
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
 
Top