Navigating Through Hyperlinks from a List with Excel VBA

Hyperlinks can also be used when organizing data on a worksheet. For example, you can use a list directly linked to hyperlinks in worksheet cells. As an example, consider preparing a statement called Examples of Laboratory Work), which uses a ListBox control that allows you to navigate to the required example file).

To prepare this example, do the following:

• Prepare the necessary example files with the .xlsm extension that will be used for hyperlink navigation, and place them in the folder.

• Open a new workbook and prepare a statement on the worksheet according. In the range C4:C8, insert hyperlinks to the corresponding prepared example files. Save the prepared file in the folder mentioned above under the name.
• In each of the files prepared in step 1, create a backward hyperlink to the file using the Add method to insert the hyperlink.

Returning to the original file via hyperlink. ThisWorkbook module

Private Sub Workbook_Open()
    Dim i As Integer
    For i = 1 To 3
        With Worksheets(i)
            .Hyperlinks.Add Anchor:=.Range("A1"), _
            Address:="D:\3-Example of using a list and hyperlinks\" & _
            "1-Examples of Laboratory Work.xlsm", _
            ScreenTip:="Return to the initial file", TextToDisplay:="BACK"
        End With
    Next
End Sub

• Place a ListBox control over the range C4:C8 by using the Insert button located on the Developer tab in the Controls group.
• Click the ListBox control and, in the Sheet1 module, enter the code from, which uses the Follow method to navigate via the specified hyperlink.

Navigating via hyperlink from a list. Sheet1 module

Private Sub ListBox1_Click()
    Hyperlinks(ListBox1.ListIndex + 1).Follow
End Sub

• In the ThisWorkbook module, enter the code , which fills the list and creates the corresponding Hyperlink objects.

Navigating via hyperlink from a list. ThisWorkbook module

Private Sub Workbook_Open()
    Worksheets(1).ListBox1.ColumnCount = 2
    Worksheets(1).ListBox1.ColumnWidths = "100;0"
    Worksheets(1).ListBox1.Clear  
    Dim lst(4, 1) As String
    lst(0, 0) = "Statements" : lst(0, 1) = "2-Statements.xlsm"
    lst(1, 0) = "Charts, Surfaces, Diagrams"
    lst(1, 1) = "3-Charts_Surfaces_Diagrams.xlsm"
    lst(2, 0) = "Arrays" : lst(2, 1) = "4-Arrays.xlsm"
    lst(3, 0) = "Text Functions"
    lst(3, 1) = "5-Text Functions.xlsm"
    lst(4, 0) = "Lists" : lst(4, 1) = "6-Lists.xlsm"   
    Worksheets(1).ListBox1.List = lst   
    Dim i As Integer
    Dim r As Hyperlink  
    For Each r In Worksheets(1).Hyperlinks
        r.Delete
    Next   
    For i = 0 To 4
        Worksheets(1).Hyperlinks.Add Anchor:=Worksheets(1).Cells(i + 4, 3), _
        Address:=lst(i, 1), TextToDisplay:=lst(i, 0)
    Next
End Sub

• Place the prepared folder with files on your computer’s D: drive and test that your hyperlinks work correctly.