Adding hyperlinks to one column

Question

I'm trying to make hyperlinks in the first column on the condition that value in the cell begins with 'W'.

It seemed to work until I moved the script from Sheet object to ThisWorkbook.

Since then when I try to copy some cells from another worksheet and paste them to active worksheet, everything what I copied is pasted as hyperlink, no matter what column or value it is. What's more, if I try to type anything in the row where the first cell is linked, the default typing mode is in the hyperlink style.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim KeyCells2 As Range
Set KeyCells2 = Range("A:A")

If Not Application.Intersect(KeyCells2, Range(Target.Address)) _
       Is Nothing Then
    On Error Resume Next
    If Target.Count = 1 Then 'this one was meant to be a fix but it didn't change a thing
        If Left(Target.Value, 1) = "W" Then
            link = "http://<mylink>" & Target.Value
            ActiveSheet.Hyperlinks.Add Target, link
        End If
    End If
End If
End Sub

Range(Target.Address) ? It's not qualified. Would Target (which is) not do? — CLR
– CLR, Commented Dec 12, 2019 at 13:56
@CLR what does it mean 'not qualified'? How to pick every cell in column A then? — lawstud
– lawstud, Commented Dec 13, 2019 at 14:07
Not qualified : you've told it to use Range ("A:A"), but you're expecting Excel to 'guess' which sheet, which workbook etc. you're referring to when you don't tell it specifically. — CLR
– CLR, Commented Dec 14, 2019 at 16:15

CLR · Accepted Answer · 2019-12-16 09:36:56Z

1

Try this, I think it will do what you need:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim KeyCells2 As Range
    Dim targetcell As Range
    Set KeyCells2 = Application.Intersect(Sh.Range("A:A"), Target, Sh.UsedRange)
    If Not KeyCells2 Is Nothing Then
        For Each targetcell In KeyCells2
            If Left(targetcell.Value, 1) = "W" Then
                link = "http://my.link." & targetcell.Value
                Sh.Hyperlinks.Add Anchor:=targetcell, Address:=link
            End If
        Next
    End If
End Sub

edited Dec 16, 2019 at 9:36

answered Dec 12, 2019 at 14:29

CLR

12.3k1 gold badge13 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

lawstud Over a year ago

with usage of your function everything is pasted properly when copying the sheet but then whole application crashes...

CLR Over a year ago

I've changed the code to only run on the intersection of A:A, the UsedRange and the pasted output. This should mean the code only runs on the smallest possible set of cells.

Collectives™ on Stack Overflow

Adding hyperlinks to one column

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related