Creating a hyperlink with a macro

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

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.

Sub Link2Cell()

  Dim LinkCell As Range
  
    Set LinkCell = Application.InputBox( _
        Prompt:=Prompt, _
        Title:=Title, _
        Default:=ActiveCell.Address, _
        Type:=8)
    
    SubAddx = LinkCell.Parent.Name & "!" & LinkCell.Address
    LinkCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
                            SubAddress:=SubAddx, _
                            TextToDisplay:=ActiveCell.Text
      
End Sub

Sincerely,
Leith Ross

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

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

Hello Dror,

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

'Written: November 18, 2007
'Author: Leith Ross
'Summary: Create a hyperlink to a cell of the user's choosing, and ask
'         to create a hyperlink back to the first hyperlink.

Sub Link2CellAndBack()

  Dim Answer As Variant
  Dim FirstAddx As String
  Dim LinkCell As Range
  Dim Prompt As String
  Dim SubAddx As String
  Dim Title As String
    
    Title = "Hyperlink Cells"
    Prompt = "Select the cell you want to hyperlink to, and click OK." & vbCrLf _
           & vbLf _
           & "You will then be asked if you want to create a link back" & vbCrLf _
           & "to the first hyperlink."
    FirstAddx = ActiveCell.Parent.Name & "!" & ActiveCell.Address
       
    On Error Resume Next
      Set LinkCell = Application.InputBox( _
          Prompt:=Prompt, _
          Title:=Title, _
          Default:=ActiveCell.Address, _
          Type:=8)
     'User selected Cancel Button
      If Err.Number = 424 Then Exit Sub
    On Error GoTo 0
    
   'Create the first Hyperlink
    SubAddx = LinkCell.Parent.Name & "!" & LinkCell.Address
    LinkCell.Hyperlinks.Add Anchor:=ActiveCell, Address:="", _
                            SubAddress:=SubAddx, _
                            TextToDisplay:=ActiveCell.Text
                            
     'Ask to create a Backward Hyperlink
      Answer = MsgBox(Prompt:="Do you want to create a backward link?", _
                      Buttons:=vbInformation + vbYesNo + vbDefaultButton2, _
                      Title:="Hyperlink Back")
        If Answer = vbYes Then
           LinkCell.Hyperlinks.Add Anchor:=LinkCell, Address:="", SubAddress:=FirstAddx
        End If
    
End Sub

Sincerely,
Leith Ross