PDA

View Full Version : Hyperlink to another sheet



dashx747
11-29-2010, 12:18 PM
Hello everyone. I need help with a macro that creates a hyperlink to the precedent of a cell in another sheet and a link back in Excel 2007.

For example, in Sheet1 there is a value in a cell (let's say in this case the cell A1), and in Sheet2 there is a cell (let's call it C1) which has the formula =Sheet1!A1. The macro, starting at Sheet2!C1, would create a hyperlink in the first cell to the right (Sheet2!D1) pointing to the cell precedent (Sheet1!A1), and then would create another hyperlink in Sheet1!B1 addressed to Sheet2!C1.

Is it possible to make such a thing? The idea is that we have a sheet with hundreds of values taken from another sheet which needed to be linked, so this would facilitate this task, but I'm having a really hard time dealing with the whole SubAddress thing.

I appreciate any help you guys can give me. Please let me know if there is any other info I should mention (I'm still a starter at VBA). Thanks.

Bob Phillips
11-29-2010, 02:05 PM
Dim sAddress1 As String
Dim sAddress2 As String

With ActiveCell

sAddress1 = Replace(Range("C1").Formula, "=", "")
ActiveSheet.Hyperlinks.Add _
Anchor:=Range("C1").Offset(0, 1), _
Address:="", _
SubAddress:=sAddress1, _
TextToDisplay:=sAddress1
sAddress2 = .Address(, , , True)
Range("C1").Offset(0, 1).Hyperlinks(1).Follow
End With

' Application.Goto sAddress1
ActiveSheet.Hyperlinks.Add _
Anchor:=ActiveCell.Offset(0, 1), _
Address:="", _
SubAddress:=sAddress2, _
TextToDisplay:=sAddress2