Home > Back-end >  How to know when a new hyperlink is added to a worksheet using an event
How to know when a new hyperlink is added to a worksheet using an event

Time:10-26

I need my code to automatically change the font of my newly created hyperlink name and change it's other font properties so I need my code to detect when a new hyperlink is added to a worksheet.

"Worksheet_Change" and "Worksheet_FollowHyperlink" didn't help.

Update: Here is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim WB As Workbook
Dim WS As Worksheet
Dim TA As ListObject
Dim TA_DateCol As ListColumn

Set WB = ThisWorkbook
Set WS = WB.Sheets(1)
Set TA = WS.ListObjects(1)

If Not Intersect(Target, TA.Range) Is Nothing Then
    'This is the part which I want my code to detect if a new hyperlink
    'is added but nothing happens my guess is it's because 'Target' type
    'in this event handler is a 'Range' not a 'Hyperlink'
End If

End Sub

Any help and advise would be appreciated.The Picture of my code so far

CodePudding user response:

Adding a hyperlink to a range does not trigger the worksheet_change event.

Therefore it is not able to detect this via code.

The solution is to update the hyperlink style of the workbook to your requirements.

There are quite a lot of tutorials that explain how to do that, e.g. https://support.microsoft.com/en-us/office/change-the-font-format-for-hyperlinks-672c2905-ad3e-40be-b281-811e68386243

  • Related