PDA

View Full Version : Solved: Cross-Reference Two Cells Using Userform with RefEdit and Hyperlink



JoeMoe
02-08-2009, 07:50 AM
I'm trying to automate the process of creating hyperlinks between two specifics cells within a workbook.

The goal is to be able to use a userform with two refedit boxes, select the two cells that I want to cross-reference, and then have Excel do the work.

I have come up with the following (with RefEdit 1 and RefEdit 2 being on a userform; CrossReference is the userform):
Private Sub CommandButton1_Click()

Dim refRange1 As Range
Set refRange1 = Range(RefEdit1)
Dim refRange2 As Range
Set refRange2 = Range(RefEdit2)

refRange1.Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=RefEdit2.Value
refRange2.Activate
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:=RefEdit1.Value

CrossReference.Hide
End Sub

The issue is that the above code only works within the same sheet and I really need the code to work between different sheets. Any help would be appreciated.

Thanks!

Bob Phillips
02-08-2009, 09:11 AM
Private Sub CommandButton1_Click()

Dim refRange1 As Range
Set refRange1 = Range(RefEdit1)
Dim refRange2 As Range
Set refRange2 = Range(RefEdit2)

refRange1.Parent.Hyperlinks.Add Anchor:=refRange1, Address:="", SubAddress:=refRange2.Address(, , , True)
refRange2.Parent.Hyperlinks.Add Anchor:=refRange2, Address:="", SubAddress:=refRange1.Address(, , , True)

CrossReference.Hide
End Sub

JoeMoe
02-08-2009, 10:54 AM
Works perfectly, thank you.