• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User


  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Automated Return Hyperlinks?

Gregg Wolin

I'd like to automatically the creation of "return" hyperlinks within a workbook.
For example if I create "Hyperlink 1" linking Cell A1 in Sheet 1 to Cell Z1 in Sheet 2, I'd like to automatically have "Hyperlink 2" created in Cell Z1 of Sheet 2 that links BACK to Cell A1 of Sheet 1.
Not sure how you'd go about that. You could certainly program a button to call a routine that a) activates the target cell (the destination of the link), then b) inserts a hyperlink in that target cell pointing back at the origin. But how would you remove the hyperlink after it's been used? Sounds unnecessarily complicated, or perhaps impossible (I haven't thought it through).

Just out of curiosity, when happens when you link to a new location and then hit the <Back> keystroke that is, <Alt-Backspace>? I don't know that Excel knows that one, but maybe...
Thanks for chiming in.

1. I’d like the destination hyperlink to be persistent. I am only contemplating this for use within the active workbook (I could see how linking to other locations would be VERY cumbersome).

2. The "Back" button does work (I put it on my QAT) but I'd like a more elegant way to trace precedent and dependent ranges. I'm just trying to automate the creation of a hyperlink back to its source range (rather than having to use the link creation function in the context menu twice).

Put another way; I wish the built-in hyperlink function had a "Create Return Link" option.

Sorry for the delay; I lost track of this one in my In box.

So if I have this right, you have a hyperlink in cell #1 that points to cell #2, and you want an easy way to create a similar hyperlink in cell #2 that points back to cell #1—and you don't need an automatic way to remove that second hyperlink later. Correct?

If so, I suppose one way to do that is to write an ordinary program that creates a hyperlink; the program can get all the information it needs if you make sure to point the cursor to cell #1 before invoking the program. The program looks at the current selection—that's cell #1. It looks inside that cell and sees the hyperlink pointing to cell #2. Now it knows what's needed, and it goes to cell #2 and creates there a hyperlink back to cell #1.

If you want to get a little fancier, I see there's a FollowHyperlink event for one worksheet or all of them. If you write a program to use this event, then when the operator clicks on a link the program will run; it can detect the cell that has the link and then create the new hyperlink as described above. Assuming you don't want all hyperlinks to generate new "back" links, you'd want your program to exercise a little discretion in its operation. How easy that would be depends on what criteria you'd want to use to make the determination.