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?
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,981
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top