Hyperlink help

[gauchofife]

I have two files. They each have several hundred names (the same names are in each file but in different orders). I want to be able to click on a name in one file and jump to that name in the other file. I assume I use Hyperlink for this but I haven't used it too much so I'm unfamiliar and can't figure it out. Is this possible? Thank you

 

[Firefly2012]

I'd be tempted to use a BeforeDoubleClick event procedure behind the worksheets on which the data sits eg assume you have Workbook1.xls and Workbook2.xls with the list of names in column A of Sheet1 in each workbook respectively. Such a code that would sit in the worksheet module of each might be:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'amend the value in "" to the other workbook you want to reference against
Const wb1 = "Workbook1.xlsm"
Dim rFound As Range
'first check that the double click has happened in a cell containing a name of interest (eg by specifying the applicable range - in this case col A):
If Not Intersect(Target, Range("A:A")) Is Nothing Then
    'now check the clicked in cell actually contains a value:
    If Not IsEmpty(Target(1).Value) Then
        'now locate the value on the other worksheet in column A (you can adjust this as required):
        Set rFound = Workbooks(wb1).Sheets(1).Range("A:A").Find(What:=Target(1).Value, LookAt:=xlWhole, MatchCase:=False, LookIn:=xlValues)
        'now check if value actually found:
        If Not rFound Is Nothing Then
            'go to found value:
            Application.Goto rFound, True
        Else
            'not found - so give suitable warning:
            MsgBox "Name not found in target sheet!"
        End If
    End If
End If
End Sub

To use it, right click on the sheet name of the sheet that contains the first set of names and select View Code - this will take you to the worksheet's code module. Paste in the above code and change the name of the workbook in the Const wb1 line to whatever the name of the other workbook is. The code currently assumes that the target worksheet will be the first in the target workbook.

Then repeat this in the second worksheet (ie copying the code in to the worksheet module and amending the relevant workbok name).

Then go and double click on a few of your names (assuming they are in column A).

 

[gauchofife]

Thanks for your help. I tried it but it didn't work. Maybe because my names are not in column A, or even in the same column in both sheets. I tried to edit the code as you instructed but I must have done it wrong. It said "Error 9" Here's some more detail:

Workbook1.xls has 30 worksheets
Workbook2.xls also has 30 worksheets
The people's names on each sheet of each workbook are the same, but in different orders. ie Sheet1 of Workbook1 has the same names as Sheet1 of Workbook2. Sheet2 of Workbook1 has the same names as Sheet2 of Workbook2, etc.
The names are in Column E in Workbook1
The names are in Colum J in Workbook2.

I want to be able to double-click on names in each sheet of Workbook1 and jump to that name in Workbook2.

Example:

Workbook1, Sheet1 has the following names in Column E
Bob Smith
Joe Stevens
Mike Jones
Mark Mathews

Workbook2, Sheet1 has the following names in Column J:
Mike Jones
Bob Smith
Mark Mathews
Joe Stevens

(There are actually hundreds of names)

Then there are 29 more sheets in each workbook, all with different names.

 

[Firefly2012]

OK, in Worksheet1 place the following code in its ThisWorkbook module in the VBE (note you may need to change the name of the target workbook in the Const wb1 line):

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

'amend the value in "" to the other workbook you want to reference against
Const wb1 = "Workbook2.xls"

Dim rFound As Range

'first check that the double click has happened in a cell containing a name of interest (eg by specifying the applicable range):

If TypeName(Sh) = "Worksheet" Then
    If Not Intersect(Target, Sh.Range("E:E")) Is Nothing Then
        'now check the clicked in cell actually contains a value:
        If Not IsEmpty(Target(1).Value) Then
            'now locate the value on the other worksheet in column A (you can adjust this as required):
            Set rFound = Workbooks(wb1).Sheets(Sh.Name).Range("J:J").Find(What:=Target(1).Value, LookAt:=xlWhole, MatchCase:=False, LookIn:=xlValues)
            'now check if value actually found:
            If Not rFound Is Nothing Then
                'go to found value:
                Application.Goto rFound
            Else
                'not found - so give suitable warning:
                MsgBox "Name not found in target sheet!"
            End If
        End If
    End If
End If



End Sub

Then in Workbook2's ThisWorkbook module place this code (note you may need to change the name of the target workbook in the Const wb1 line):

Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)

'amend the value in "" to the other workbook you want to reference against
Const wb1 = "Workbook1.xlsm"

Dim rFound As Range

'first check that the double click has happened in a cell containing a name of interest (eg by specifying the applicable range):

If TypeName(Sh) = "Worksheet" Then
    If Not Intersect(Target, Sh.Range("J:J")) Is Nothing Then
        'now check the clicked in cell actually contains a value:
        If Not IsEmpty(Target(1).Value) Then
            'now locate the value on the other worksheet in column A (you can adjust this as required):
            Set rFound = Workbooks(wb1).Sheets(Sh.Name).Range("E:E").Find(What:=Target(1).Value, LookAt:=xlWhole, MatchCase:=False, LookIn:=xlValues)
            'now check if value actually found:
            If Not rFound Is Nothing Then
                'go to found value:
                Application.Goto rFound
            Else
                'not found - so give suitable warning:
                MsgBox "Name not found in target sheet!"
            End If
        End If
    End If
End If



End Sub

and delete all the code I previously gave you. Now try double clicking on the names in col E in workbook1 and in col J in workbook2.

 

[gauchofife]

Wow, so cool. Thank you very much for your help firefly. Works perfect.

 

[Firefly2012]

You're welcome