Excel 2007 : Sending Cell Data on Hyperlink Click

**bush1282** · 08-22-2011, 06:54 PM

I have a column with a large number of rows that contain activity numbers - 1 activity number for each cell. Each of these cells has a hyperlink set, so that when the user clicks on the link, they are taken to a different cell in the same sheet.
I wanted to be able to send the cells data (the activity number) to the destination cell.

For example:
C2 = 1-YKCQ8O8
C3 = 1-YRSRNMB
C4 = 1-Z1AGVE0

M5 is my destination cell.

I hoping to be able to get cell M5 to display 1-YKCQ8O8 if I click on that link in cell C2.
But, M5 has to be able to display 1-Z1AGVE0 if I click on the link in cell C4, etc.

Any ideas?

While searching for an answer, I was able to find this:

Please Login or Register  to view this content.

That would do nothing for the the clickable hyperlink, but if you double-clicked the cell, it would send the data to cell M5.

Any help would be greatly appreciated.

**davesexcel** · 08-22-2011, 07:48 PM

This will work

Please Login or Register  to view this content.

**bush1282** · 08-22-2011, 08:27 PM

WOW, Dave - Thanks. That worked great!

I am quite familiar with VB for developing desktop applications, however this is the first time I have done anything with excel. Could you go into a little detail with the code on what is doing what?
Just for my personal knowledge - As in:

Application.Intersect
Cancel = True
rng = Target
Just hoping for a little clarification on what that is - but as far as the problem I was having...that absolutely fixed it.
Thank you so much!

**davesexcel** · 08-24-2011, 07:08 PM

Application.Intersect - determins the target range
Cancel = True - when you double click a cell, excel wants you to edit the cell, this stops that from happening.
rng = Target- we set rng to be range("M5"), when you double click a cell in the target range, M5 is equal to the cell double clicked..