Creating a hyperlink with a macro

**Dror** · 11-17-2007, 12:40 PM

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** · 11-17-2007, 12:47 PM

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** · 11-17-2007, 01:28 PM

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim IB As String
If Target.Row = 1 Then Exit Sub
        If Target.Cells.Count > 1 Then Exit Sub
            If Target.NumberFormat = "[$$-409]#,##0.00" Then
            IB = InputBox("Enter Destination Cell In This Format A1", "Hyperlink Destination", "Type Here")
    ActiveSheet.Hyperlinks.Add Anchor:=Target.Offset(0, 1), Address:="", SubAddress:= _
        "" & IB, TextToDisplay:=Range(IB).Text
                Else: Exit Sub
            End If
End Sub

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

**Dror** · 11-17-2007, 02:28 PM

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** · 11-17-2007, 03:36 PM

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