Forum Discussion

Copper Contributor

Jun 05, 2023

Solved

Dynamic links creation

Hi,

Is there any way (even via VBA script) how to do this:
1. Having a column where cell values can be selected only using a pre-defined drop-down list (data source is on a different sheet)

2. I need to create a link on that original data source cell

Example: Sheet2 contains A1-Apple, A2-Orange, A3-Peach
In Sheet1 column A I need to be able to select only Apple, Orange or Peach and based on my selection I need to have also a hyperlink on that cell which will lead to the selected item on Sheet2 (so if for Sheet1!A1 I choose Peach from drop-down, I need to see the Peach in the cell and have a link to Sheet2!A3)

I've tried something already but I got a "circular reference error" 😞

Thanks

excel

Formulas and Functions

It seems like you do not need a hyperlink in A1. After all, clicking on the cell to select a different fruit would trigger the existing hyperlink instead, right? You could generate a hyperlink in a separate cell. But that's a second action for the user to get there. Instead…

Build the validation for cell A1 as usual, for example, as described in Excel Drop Down Lists - Data Validation. Within a Worksheet_Change event handler for Sheet1, when cell A1 changes, do a lookup to find the address on Sheet2 for the selected fruit, and execute the Application.Goto method for that address.

Here is possible code for the event handler:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngFruitType    As Range
    Dim rngChgdCellsOfInterest  As Range
    Dim strFruitType    As String
    '
    Dim objSheet2   As Worksheet
    Dim in4Row  As Long
    
    '----   If a different type of fruit was selected...
    Set rngFruitType = Range("A1")
    '
    Set rngChgdCellsOfInterest = Intersect(Target, rngFruitType)
    If rngChgdCellsOfInterest Is Nothing Then
        GoTo WkshtChg_Exit
    End If
    '  --   ...
    strFruitType = rngFruitType.Value
    If strFruitType = "" Then
        '...the changed cell(s) did not include the Fruit Type cell.
        GoTo WkshtChg_Exit
    Else
        '  --   Find the matching row on Sheet2.
        Set objSheet2 = Sheets("Sheet2")
        in4Row = WorksheetFunction.Match(strFruitType _
                , objSheet2.Range("A1:A99"))
        '  --
        Application.Goto "Sheet2!R" & in4Row & "C1"
    End If
    
WkshtChg_Exit:
    Exit Sub
End Sub

3 Replies

SnowMan55

Bronze Contributor

Jun 06, 2023

TomasCiganek

Here is possible code for the event handler:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rngFruitType    As Range
    Dim rngChgdCellsOfInterest  As Range
    Dim strFruitType    As String
    '
    Dim objSheet2   As Worksheet
    Dim in4Row  As Long
    
    '----   If a different type of fruit was selected...
    Set rngFruitType = Range("A1")
    '
    Set rngChgdCellsOfInterest = Intersect(Target, rngFruitType)
    If rngChgdCellsOfInterest Is Nothing Then
        GoTo WkshtChg_Exit
    End If
    '  --   ...
    strFruitType = rngFruitType.Value
    If strFruitType = "" Then
        '...the changed cell(s) did not include the Fruit Type cell.
        GoTo WkshtChg_Exit
    Else
        '  --   Find the matching row on Sheet2.
        Set objSheet2 = Sheets("Sheet2")
        in4Row = WorksheetFunction.Match(strFruitType _
                , objSheet2.Range("A1:A99"))
        '  --
        Application.Goto "Sheet2!R" & in4Row & "C1"
    End If
    
WkshtChg_Exit:
    Exit Sub
End Sub

TomasCiganek

Copper Contributor

Jun 06, 2023

Hi, SnowMan55

Thanks for the guidance, my simplified solution which works looks like:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column = 14 Then 'Reacts to changes only in defined column
        Set ChangedCell = Cells(Target.Row, Target.Column)
        ChangedValue = ChangedCell.Value ' Get a new value
        Address = Cells(WorksheetFunction.Match(ChangedValue, Worksheets("Deliverables").Range("A1:A30"), 0), 1).Address 'Find a cell address of the selected value in other sheet
        ActiveSheet.Hyperlinks.add Range(ChangedCell.Address), Address:="", SubAddress:="Deliverables!" & Address, TextToDisplay:=ChangedValue 'Add a new hyperlink
    End If
End Sub

peiyezhu
Bronze Contributor
Jun 06, 2023
have a link to Sheet2!A3

how about insert formula hyperlink?