Question:  I have created a hyperlink from one worksheet to another within the same file. But when the linked worksheet is pulled up, I want to populate the designated field with some text. The text will change depending on the hyperlink selected, but all the links will connect to the same worksheet and the same field. The field I am wanting to populate controls numerous lookup functions.

HELP!!  Can this be done?

Answer:  Yes, this can be done by utilizing macros on two Excel workbook "events".

Let's take a look at an example.

Download Excel spreadsheet (as demonstrated below)

In our spreadsheet, there are two sheets called Hyperlinks and Destination. The Hyperlinks sheet contains three hyperlinks in cell A3, A6, and A9. All three hyperlinks point to cell B2 in the Destination sheet

.

When a hyperlink is clicked, the Destination sheet is activated and cell B2 will display text showing which hyperlink was selected. In our example below, we've selected "Hyperlink #1" in cell A3.

You can view the macros by pressing Alt-F11 and double-clicking on the ThisWorkbook object in the left window.

There are three components to this solution:

1. There is a global variable called GSourceCell which contains the cell reference when the hyperlink was selected.


2. There is a workbook event called "SheetSelectionChanged". When this event fires, the GSourceCell variable will be populated


3. There is a workbook event called "SheetFollowHyperlink". When this event fires, cell B2 in the Destination sheet will be populated with text.

Macro Code:

The macro code looks like this:

Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)

    'Update cell B2 in Destination sheet based on the origin of hyperlink
    If Sh.Name = "Hyperlinks" Then
        If GSourceCell = "A3" Then
            Sheets("Destination").Range("B2").Value = "Hyperlinked from cell A3"
        ElseIf GSourceCell = "A6" Then
            Sheets("Destination").Range("B2").Value = "Hyperlinked from cell A6"
        ElseIf GSourceCell = "A9" Then
            Sheets("Destination").Range("B2").Value = "Hyperlinked from cell A9"
        Else
            Sheets("Destination").Range("B2").Value = ""
        End If
    End If

End Sub

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)

    If Sh.Name = "Hyperlinks" Then
        'Capture last active cell on Hyperlinks worksheet and store in global variable
        GSourceCell = Target.Address(False, False)
    End If

End Sub