[321Tommy]

Is there any way to set up two hyperlinks at once. One linking from one sheet (say A) to another (say B), into a specific cell, and at the same time setting up another hyperlink in the cell linked to on sheet B, linking back to the cell on sheet A where the first hyperlink exists.

At the moment I am having to set up two hyperlinks everytime I want to do this which is very time consuming.

Is there anyone out there that can help?

[Advertisements]

Hi 321Tommy. Welcome to Geeks to Go!

I don't really understand what you're trying to accomplish. Are you referencing cells in Sheet B in a formula that resides in a cell in Sheet A? Or are you really creating a hyperlink inside a cell in Sheet A to a cell in Sheet B so that when you click that cell in Sheet A, it opens Sheet B at that cell?

Please give as much information as possible.

[piper]

Hi 321Tommy. Welcome to Geeks to Go!

I don't really understand what you're trying to accomplish. Are you referencing cells in Sheet B in a formula that resides in a cell in Sheet A? Or are you really creating a hyperlink inside a cell in Sheet A to a cell in Sheet B so that when you click that cell in Sheet A, it opens Sheet B at that cell?

Please give as much information as possible.

[321Tommy]

What i'm trying to do is set up two links at the same time.

At work I use excel alot. In each workbook I produce the first worksheet is a summary of all the information contained in the workbook. The detailed workings are included on later worksheets in the workbook.

To make it easy for others to see how I have arived at the figures I have included on summary page I hyperlink to the releavant document in the workbook where those detailed wokings are.

I also like to include a hyperlink from the worksheet that includes the workings to the summary workbook page so that others can see where the workings have been included on the summary worksheet.

At the moment this involves setting up two hyperlinks.

Typically I will set the hyperlink from the summary worksheet (in the cell to the left of the one which contains the information that I plan to referrence, i.e. if the data is in cell "D4", I will set up the hyperlink in "C4") to link into the cell directly to the left of the cell which contains the data that I am referencing (for example, if the information referenced is contained in cell "J25" I will link to "I25".

I then set up another hyperlink in the cell to the left of the information being referenced in the workings worksheet to link back to the cell directly to the left of the information being referenced in the summary worksheet. Using the above examples this would create a hyperlink in "I25" (on the workings worksheet) that linked to "C4" (on the summary worksheet).

The two cells directly to the left of each set of information should now link to each other allowing easier naviagtion of the data. One click will take you from the summary sheet to the workings, and another will take you from the workings to the summary.

Is it possible to set up both hyperlinks in one step, i.e so I dont have to go through the process of setting up both hyperlinks separately?

[dsm]

321Tommy,
I have been working on your problem for the last couple of days.
The following code will prompt the user to click 2 cells and allow them to edit the display text before creating a hyperlink between the 2 cells which can be on seperate sheets.

Try this code

Dim rCell_01 As Range
Dim rCell_02 As Range
Dim sTxt_01 As String
Dim sTxt_02 As String
Dim sCellAdd_01 As String
Dim sCellAdd_02 As String
Dim SheetStart As Worksheet
Dim Sheet_01 As Worksheet
Dim Sheet_02 As Worksheet

Set SheetStart = Application.ActiveWorkbook.ActiveSheet

Set rCell_01 = Application.InputBox("Click Cell_01", "Select Cell", Type:=8)
Set Sheet_01 = rCell_01.Worksheet
sTxt_01 = Application.InputBox("Enter Text to display in Cell_01", "Enter Text", rCell_01.Value)

Set rCell_02 = Application.InputBox("Click Cell_02", "Select Cell", Type:=8)
Set Sheet_02 = rCell_02.Worksheet
sTxt_02 = Application.InputBox("Enter Text to display in Cell_02", "Enter Text", rCell_02.Value)

sCellAdd_01 = rCell_02.Worksheet.Name & "!" & rCell_02.Address
sCellAdd_02 = rCell_01.Worksheet.Name & "!" & rCell_01.Address

Sheet_01.Select
rCell_01.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sCellAdd_01, TextToDisplay:=sTxt_01
Range("A1").Select

Sheet_02.Select
rCell_02.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
sCellAdd_02, TextToDisplay:=sTxt_02
Range("A1").Select

SheetStart.Select
Range("A1").Select

Debug.Print
Debug.Print rCell_01.Value
Debug.Print rCell_01.Formula
Debug.Print rCell_01.Address
Debug.Print rCell_01.Worksheet.Name
Debug.Print rCell_01.Worksheet.Parent.Name
Debug.Print
Debug.Print rCell_02.Value
Debug.Print rCell_02.Formula
Debug.Print rCell_02.Address
Debug.Print rCell_02.Worksheet.Name
Debug.Print rCell_02.Worksheet.Parent.Name
Debug.Print

End Sub



dsm