Creating a hyperlink with a macro

[Dror]

Hi all,

I would like your help in creating a hyperlink in a sheet, pointing from the selected cell to a different cell in the same sheet.

The user is supposed to select a cell, and activate the macro.
The macro should pop up an inputbox to allow the user to select (point) a different cell in the sheet.
The macro should then create a hyperlink to this cell.

The cell will usually contain a Dollar value, and the target cell will usually contain a name of a part.

If possible, I would also like the macro to show, in the cell adjacent to the cell that contains the link, the value of the target cell. For example, the user will see in the linked cell a certain Dollar value (i.e. $1000) and next to it, he will see the value of the linked/target cell (i.e. the part's name).

I have tried and tried but never succeeded in doing that… even when I managed to create a link, it didn't work (I received an error message stating that there is an invalid reference).

Your help will be highly appreciated.

Regards,

Dror

[davesexcel]

Hi,
Let's see what you have so far, and go one step at a time.
Zip an copy of what you have and attach it to your next post,so somebody will be able to look at it

[Simon Lloyd]

Try this:

Please Login or Register  to view this content.

EDITED AS PREVIOUS CODE DIDN'T WORK CODE ABOVE WORKS FINE!

[Dror]

Simon, thanks for the code but I can't get it to work.

Attached is a sample of the file as i need it to look (it is just a rough format and will change for sure...).
In any case, you can see "my" macro (which does not work) as well as Simon's macro (in sheet1).

Thanks for your time,

Dror

[Leith Ross]

Hello Dror,

This macro uses the ActiveCell's text as the text for the Hyperlink. The Hyperlink will go to any cell on any Worksheet the user chooses with the InputBox.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Dror]

Works like a charm.
Thank you all for your help!

[Dror]

Hi there,
It’s me again… Can I possibly ask for another favor?

Now that this hyperlink macro works so nicely, I thought it would be a great idea if while linking cell X to cell Y, the macro will create a “back link” from Y to X.

Since all links (in this specific file) will always be 1:1 (and unique), I want the user to be able to navigate easily between the origin cell and the target cell and BACK.
If there is an automatic back-link, the user will be able to click on the origin cell, jump to the target cell (and check whatever he wants) and then click the same cell and jump back to the origin cell (sort of like a “back” feature).

I think the best way will be to do it a two-step action. The first will be to create the initial link (this is what the macro is doing now) and the second step will be to popup another box and ask the user if he wants to create a back-link (if “yes” – do the back-link to the origin cell. If “no” – exit the macro).

I thank you in advance for your time and support,

Regards,

Dror

[Leith Ross]

Hello Dror,

This macro creates a hyperlink to the cell of the user's choosing, and then asks to create a backward hyperlink.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Dror]

WOW !!!! I can't thank you enough. You are a life saver.
I'll give it a test run later on today (I've got to run now... )
All the best,
Dror