MS Excel 2013: Update cell based on hyperlink selected
See solution in other versions of Excel:
Question: In Microsoft Excel 2013, 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.
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 #2" in cell A6.
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:
- There is a global variable called GSourceCell which contains the cell reference when the hyperlink was selected.
- There is a workbook event called "SheetSelectionChanged". When this event fires, the GSourceCell variable will be populated
- 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
Advertisements