Hyperlinks between two cells

aa2000

Board Regular
Joined
Aug 3, 2011
Messages
87
Hi folks

I have two workbooks one which contains a list of experiments and the other which contains the experiment data.

I am trying to write a macro that will go through the list in the first book and the experiments in the second and create a hyperlink between a name and the corresponding experiment.

Now I can get the macro to match the two cells, but I am struggling to understand the add hyperlink function in VBA. How can I get it to create a hyperlink between 2 cells in different worksheets?
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

aa2000

Board Regular
Joined
Aug 3, 2011
Messages
87
Ok I've made some progress with this and I can get the macro to create hyperlinks between the two files if the two values match. However I cannot get it the hyperlink to open a specific sheet.

Heres the code
Code:
Sub Links()

Set Lrange = Sheet3.Range("A20:A50")
Set Lrange2 = Sheet4.Range("A1:A20")


For Each cell In Lrange

If cell.Value Like "Experiment*" Then
s = cell.Value
a = Trim(Mid(Replace(s, " ", Space(100)), 100, 100))

For Each rcell In Lrange2

If rcell.Value = a Then
Range(rcell.Address).Hyperlinks.Add Anchor:=Range(rcell.Address), Address:=ActiveWorkbook.Path + "/" + ActiveWorkbook.Name, SubAddress:=cell.Address, TextToDisplay:=Range(rcell.Address).Text
End If
Next rcell
End If
Next cell

End Sub
I know that the sheet name should go in the sub address section, but how can I actually reference the sheet name?

Cheers
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175
The SubAddress section normally looks like this:-
SubAddress:= "Sheet3!A1"

You'll need to change it to your specific requirements.
Must admit, I've never used hyperlinks in this manner though.
 

aa2000

Board Regular
Joined
Aug 3, 2011
Messages
87
Thanks Richard

I think I can adapt that format to work for me. This is also my first time using hyperlinks like this, and whilst it does work till I add the sub address part, getting the hyperlink to open a specific sheet/cell is a little trickier. I'll let you know how it turns out!

Cheers
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175
No problem.
Post on here if you run into any snags.
 

aa2000

Board Regular
Joined
Aug 3, 2011
Messages
87
Well I hit a snag (big surprise that...)

Anyway I have the current code:

Code:
Sub Links()

Set Lrange = ActiveSheet.Range("A20:A50")
Set Lrange2 = Sheet4.Range("A1:A20")


For Each cell In Lrange

If cell.Value Like "Experiment*" Then
s = cell.Value
a = Trim(Mid(Replace(s, " ", Space(100)), 100, 100))

For Each rcell In Lrange2

If rcell.Value = a Then
t = rcell.Value
u = rcell.Address

u = ThisWorkbook.Path + ThisWorkbook.Name + "-" + "Sheet4" + u
MsgBox u

Range(rcell.Address).Hyperlinks.Add Anchor:=u, Address:=ThisWorkbook.Path + "/" + ActiveWorkbook.Name, SubAddress:=ActiveSheet.Name + cell.Address, TextToDisplay:=t
End If

Next rcell
End If
Next cell

End Sub
Now a bit more explanation:
cell is in workbook Book14
rcell is in workbook Book2

When the code from my previous post is run it does not remember that rcell is in book 2 and puts the hyperlink in the equivalent address of the active sheet of book 14. So if rcell.Address = A5, then the hyperlink is in cell A5 of sheet1 of book14.

Now I'm trying to get the code place the hyperlink in rcell.Address of book2.
Any suggestions?

Cheers
 

RichardMGreen

Well-known Member
Joined
Feb 20, 2006
Messages
2,175
How are using opening the other workbook (or grabbing it's name)?
I don't like using Activeworkbook as it has a chance of referring to the wrong one.
 

aa2000

Board Regular
Joined
Aug 3, 2011
Messages
87
I am not sure I understood your question properly, but the workbook to which this code belongs is selected by "ThisWorkbook", with ActivWorkbook being the active on in which the experiment data lies.

I've done it this way, because the workbook with the experiment data is created by another series of macros, so its easier to run a macro on that from another workbook.

Does that actually make sense? If not let me know and I'll try and explain it better. This project however has achieved its main goals, and this hyperlinks macro is just an option which would be nice, but is not mandatory, so I think I may leave it as it is for the time being.

Cheers
 

Watch MrExcel Video

Forum statistics

Threads
1,099,798
Messages
5,470,838
Members
406,730
Latest member
shubhangishastri

This Week's Hot Topics

Top