totn Excel

MS Excel 2010: Update cell based on hyperlink selected

Question: In Microsoft Excel 2010, 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 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.

Microsoft Excel

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 #3" in cell A9.

Microsoft Excel

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:

Dim GSourceCell As String

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