Excel Hyperlink Help

[everbake]

How do I create a link that when you type in the cell, it adds the text to the end of the link?

I.E.

I type 123 in cell, it then creates a hyperlink to http://www.mysite.com/id/123 and displays 123 in cell.

I type 931 in cell, it then creates a hyperlink to http://www.mysite.com/id/931 and displays 931 in cell.

 

[CWatts]

Open the vba editor, and double click on the sheet you want this code to auto run on.

In the first drop down select "Worksheet" and the second drop down select "Change"

Replace the code with this:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LinkAddress As String
    LinkAddress = "http://www.mysite.com/id/" & Target.Value
    Target.Hyperlinks.Add Target, LinkAddress
End Sub

If you are ever going to have links like 011 or other leading zeros, make sure the cells are formatted as text.

This does it to every cell on the sheet where data is entered so you'll need to be more specific if you need other limitations.

 

[everbake]

Looking good so far.

Yes, it would be a specific range of cells let's say for example B4 to B18.

 

[CWatts]

Ok, give this a go then

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LinkAddress As String
    If Intersect(Target, Range("B2:B18")) Is Nothing Then
    Else
        LinkAddress = "[URL]http://www.mysite.com/id/[/URL]" & Target.Value
        Target.Hyperlinks.Add Target, LinkAddress
    End If
End Sub

 

[everbake]

Everything is almost working, except when I want to clear/delete the link in the cell, it gives me a run-time error '1004' application-defined or object-defined error.

 

[CWatts]

Doh, oversight on my part:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LinkAddress As String
    If Target.Value = "" Then
        Target.Hyperlinks.Delete
        Else
            If Intersect(Target, Range("B2:B18")) Is Nothing Then
            Else
            LinkAddress = "[URL]http://www.mysite.com/id/[/URL]" & Target.Value
            Target.Hyperlinks.Add Target, LinkAddress
            End If
        End If
End Sub

 

[everbake]

ah, works perfectly, thank you!