Auto hyperlink same cells between them VBA

[ToseSenpai]

Hi all,

There is a way to hyperlink same cells between them with a vba macro?
For example, i got 3 sheets with the same numbers in every sheets, there is a way to automatically hyperlink them without do it manually?

I mean that when I click on a cell with the value it takes me directly to the another cell with the same value in the file excel, and so on...

I hope I explained myself!

Thank you all for your help!

 

[offthelip]

Rather than using a hyperlink you can do it by using the doubleclick event. This code takes the value of the cell in column A on sheet 1 then search sheet 2 column A for the value and select that cell on sheet 2. You can obviously put similar code in sheet 2 to take you to sheet3 and in sheet 3 to take you back to sheet 1
Put this code in the worksheet code for sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' with thanks to Ron de Bruin for the vba find code
Dim rng As Range
If Target.Column = 1 Then
Findstring = Target.Value
With Sheets("Sheet2").Range("A:A")
Set rng = .Find(What:=Findstring, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then
                Application.Goto rng, True
            Else
                MsgBox "Nothing found"
            End If
 End With
 End If
   
End Sub

 

[ToseSenpai]

Rather than using a hyperlink you can do it by using the doubleclick event. This code takes the value of the cell in column A on sheet 1 then search sheet 2 column A for the value and select that cell on sheet 2. You can obviously put similar code in sheet 2 to take you to sheet3 and in sheet 3 to take you back to sheet 1
Put this code in the worksheet code for sheet1

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' with thanks to Ron de Bruin for the vba find code
Dim rng As Range
If Target.Column = 1 Then
Findstring = Target.Value
With Sheets("Sheet2").Range("A:A")
Set rng = .Find(What:=Findstring, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not rng Is Nothing Then
                Application.Goto rng, True
            Else
                MsgBox "Nothing found"
            End If
 End With
 End If
  
End Sub

Ty fot the solution
There is a way for search instead of only sheet 2 in all workbook?

 

[offthelip]

You mean if it is not found on sheet 2 you want to go on to sheet 3 , etc, etc??

 

[ToseSenpai]

You mean if it is not found on sheet 2 you want to go on to sheet 3 , etc, etc??

Yes

 

[offthelip]

try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' with thanks to Ron de Bruin for the vba find code
Dim rng As Range
If Target.Column = 1 Then
Findstring = Target.Value
For i = 1 To Worksheets.Count
    If Worksheets(i).Name <> ActiveSheet.Name Then
        With Worksheets(i).Range("A:A")
        Set rng = .Find(What:=Findstring, _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                    If Not rng Is Nothing Then
                       Application.Goto rng, True
                       Exit For
                    End If
         End With
    End If
Next i
End If
End Sub

 

[ToseSenpai]

try this:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
' with thanks to Ron de Bruin for the vba find code
Dim rng As Range
If Target.Column = 1 Then
Findstring = Target.Value
For i = 1 To Worksheets.Count
    If Worksheets(i).Name <> ActiveSheet.Name Then
        With Worksheets(i).Range("A:A")
        Set rng = .Find(What:=Findstring, _
                                    After:=.Cells(.Cells.Count), _
                                    LookIn:=xlValues, _
                                    LookAt:=xlWhole, _
                                    SearchOrder:=xlByRows, _
                                    SearchDirection:=xlNext, _
                                    MatchCase:=False)
                    If Not rng Is Nothing Then
                       Application.Goto rng, True
                       Exit For
                    End If
         End With
    End If
Next i
End If
End Sub

Ty very much! It works